结构化查询语言/UPDATE 2

提示：注意并禁用AUTOCOMMIT。

本页面提供两种额外技术，作为对UPDATE 命令的扩展，该命令显示在之前页面之一上

• 计算在运行时分配给列的值。
• 使用复杂的子查询作为 WHERE 子句中的搜索条件。

运行时评估值

分配给列的值可以通过相关或不相关的标量值子查询在相关联的表或其他表中计算。在许多用例中都使用了这种技术：线性或百分比增加值，使用来自同一表或其他表的 value，...。情况类似于在关于INSERT 命令的页面中描述的情况。

-- The average weight of all persons is stored in column 'weight' of the first four persons.
UPDATE person SET
  -- dynamic computation of a value
  weight = (SELECT AVG(weight) FROM person)
  --  weight = (SELECT * FROM (SELECT AVG(weight) FROM person) tmp) -- MySQL insists on using an intermediate table
WHERE  id < 5;

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

子查询可以使用当前正在更新行的值。在下一个示例中，人员会收到其家庭的平均体重。为了计算此平均体重，需要使用实际处理行的“lastname”列。

-- The subquery is a 'correlated' scalar value subquery.
UPDATE person p SET
  -- 'p.lastname' refers to the lastname of the actual row. The subquery bears all rows in mind, not only those with 'id >= 5'.
  weight = (SELECT AVG(weight) FROM person sq WHERE sq.lastname = p.lastname)
  -- A hint to MySQL users: MySQL does not support UPDATE in combination with a correlated subquery
  -- to the same table. Different tables work. MySQL has a different, non-standard concept: multi-table update.
WHERE  id >= 5;

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

WHERE 子句中的子查询

WHERE 子句确定 UPDATE 命令涉及的表的哪些行。此 WHERE 子句与 SELECT 或 DELETE 命令的 WHERE 子句具有相同的语法和语义。它可能包含布尔运算符的复杂组合，如 ANY、ALL 或 EXISTS 等谓词，以及在SELECT: Subquery中描述的递归子查询。

-- UPDATE rows in the 'person' table based on the results of a subquery to the 'contact' table.
-- In the example, persons with more than 2 contact information are affected.
UPDATE person
SET    firstname = 'Has many buddies'
WHERE  id IN
  (SELECT person_id
   FROM   contact
   GROUP BY person_id
   HAVING count(*) > 2
  );

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

该命令在 person 表中执行 UPDATE 操作，但受影响的行由 contact 表中的子查询确定。这种从其他表中获取信息的技术为修改数据提供了非常灵活的策略。

在子查询中选择 0 行不是错误。在这种情况下，DBMS 将像往常一样执行 UPDATE 命令，并且不会抛出任何异常。（对于 SELECT 或 DELETE 语句中的子查询，情况也是如此。）

练习

将所有 firstname 小于 5 个字符的 persons 的 firstname 赋值为“Short firstname”。

点击查看解决方案

-- Hint: Some implementations use a different function name: length() or len().
UPDATE person
SET    firstname = 'Short firstname'
WHERE  character_length(firstname) < 5;

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;

将所有没有爱好的 persons 的 firstname 赋值为“No hobby”。

点击查看解决方案

UPDATE person
SET    firstname = 'No hobby'
WHERE  id NOT IN
  (SELECT person_id
   FROM   person_hobby
  );

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;

将所有执行“Underwater Diving”或“Yoga”爱好的 persons 的 firstname 赋值为“Sportsman”。

点击查看解决方案

UPDATE person
SET    firstname = 'Sportsman'
WHERE  id IN
  -- The subquery must join to the table 'hobby' to see their column 'hobbyname'.
  (SELECT ph.person_id
   FROM   person_hobby ph
   JOIN   hobby        h  ON ph.hobby_id = h.id
   AND    h.hobbyname IN ('Underwater Diving', 'Yoga')
  );

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;