提示:注意并关闭 AUTOCOMMIT

本页面介绍了两种用于扩展 UPDATE 命令的技术:

  1. 在运行时计算赋值给列的值。
  2. WHERE 子句中使用复杂的子查询作为搜索条件。

运行时计算值

可以通过相关或非相关的标量子查询,在同一张表或其他表上动态计算值并将其赋值给列。这种技术在多种情况下都会用到,例如:线性或按百分比增加值、从同一张表或其他表中提取值等。这种用法类似于 INSERT 命令中的动态插入。

示例 1:动态计算值

以下示例将所有人的平均体重存储到前四个人的 weight 列中。

-- 将所有人的平均体重赋值给前四个人的 weight 列
UPDATE person SET
  weight = (SELECT AVG(weight) FROM person) -- 动态计算值
WHERE  id < 5;

-- 检查结果
SELECT * FROM person;

-- 撤销更改
ROLLBACK;

示例 2:相关子查询

子查询可以使用当前正在更新的行的值。例如,以下示例中,每个人都将获得其家庭成员的平均体重。

-- 使用相关标量子查询计算家庭平均体重
UPDATE person p SET
  weight = (SELECT AVG(weight) FROM person sq WHERE sq.lastname = p.lastname)
WHERE  id >= 5;

-- 检查结果
SELECT * FROM person;

-- 撤销更改
ROLLBACK;

注意:对于 MySQL 用户,MySQL 不支持在同一张表中使用相关子查询与 UPDATE 命令。对于不同的表,MySQL 支持多表更新(multi-table update)。


WHERE 子句中的子查询

WHERE 子句用于决定 UPDATE 命令影响哪些行。其语法和语义与 SELECTDELETE 命令的 WHERE 子句相同。可以包含布尔操作符、谓词(例如 ANYALLEXISTS),以及递归子查询。

示例:使用子查询确定更新行

以下示例更新了拥有超过两条联系方式的人的 firstname 列。

-- 根据 contact 表的结果更新 person 表
UPDATE person
SET    firstname = 'Has many buddies'
WHERE  id IN
  (SELECT person_id
   FROM   contact
   GROUP BY person_id
   HAVING count(*) > 2
  );

-- 检查结果
SELECT * FROM person;

-- 撤销更改
ROLLBACK;

要点总结

  • 子查询可以在 UPDATE 的赋值部分或 WHERE 子句中使用。
  • 如果子查询未选择任何行,则 UPDATE 命令不会抛出异常,而是正常执行(这与 SELECTDELETE 命令中的子查询一致)。
  • 使用相关子查询时需要注意性能问题,因为子查询会对每一行单独执行。
最后修改: 2025年01月28日 星期二 13:29