SQL UPDATE：修改已有的行

Q: 如果执行不带 WHERE 的 UPDATE 会怎样？

表中的每一行都会被更新。唯一的保护措施是尚未提交的事务、safe updates 模式或备份。执行 UPDATE 之前，请务必检查 WHERE 子句。

Q: 如何在 SQL 中更新多列？

在 SET 中列出它们，用逗号分隔：UPDATE customer SET first_name = 'ALICE', last_name = 'COOPER' WHERE customer_id = 42;。

Q: 如何用另一张表中的值更新一张表？

在 WHERE 或 SET 中使用子查询，或者使用多表更新。在 MySQL 中：UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col WHERE ...；在 PostgreSQL 中：UPDATE t1 SET col = t2.col FROM t2 WHERE t1.id = t2.id。

第 8.2 课 · 阅读时间：约 7 分钟 · 练习题 ↓

SQL UPDATE 语句修改表中已有行的值。在本课中，你将学习 UPDATE ... SET ... WHERE 语法、如何更新多列以及根据当前值计算新值、如何根据另一张表更新行，以及如何确保更新只修改你想要修改的行。

INSERT 添加新行；UPDATE 则用来修正和维护已有的行：客户更换了电子邮件、价格上涨、状态从“活跃”变为“非活跃”。一条 UPDATE 既可以修改一行，也可以修改数百万行，因此它与 DELETE 一样需要谨慎对待。

SQLtest 上的示例数据库是只读的，因此在那里执行这些语句会返回权限错误。示例使用 Sakila 模式。

SQL UPDATE：SET 赋予新值，WHERE 选择要修改的行，没有 WHERE 的 UPDATE 会修改表中的所有行

UPDATE 语法

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

UPDATE 指定表。
SET 列出各列及其新值。
WHERE 选择要修改的行。没有 WHERE 时，表中的每一行都会被更新。

更新一行

UPDATE customer
SET email = 'mary.smith@example.com'
WHERE customer_id = 1;

结果：修改了一行；主键上的条件恰好匹配一位客户。

按主键过滤是修改特定行最安全的方式：姓名可能重复，而主键不会。

更新多列

在 SET 中列出多个赋值，用逗号分隔：

UPDATE customer
SET first_name = 'ALICE',
    last_name = 'COOPER',
    email = 'alice.cooper@example.com'
WHERE customer_id = 42;

结果：用一条语句修改了 42 号客户的三列。

根据当前值计算

SET 的右侧可以使用列的当前值：

UPDATE film
SET rental_rate = rental_rate + 1.00
WHERE rating = 'NC-17';

结果：全部 210 部 NC-17 电影的租赁价格提高 1 美元。

SET 中的所有表达式都使用更新之前的值，因此在标准 SQL 中 SET a = b, b = a 会交换两列的值。MySQL 是个例外：它按从左到右的顺序执行赋值，后面的赋值看到的是已经更新的新值。

根据另一张表更新行

要按另一张表的条件修改行，可以在 WHERE 中使用子查询。把所有喜剧片的租赁价格提高 10%：

UPDATE film
SET rental_rate = rental_rate * 1.10
WHERE film_id IN (
    SELECT fc.film_id
    FROM film_category AS fc
    INNER JOIN category AS c ON c.category_id = fc.category_id
    WHERE c.name = 'Comedy'
);

结果：58 部喜剧片的价格提高 10%。

MySQL 和 MariaDB 不允许子查询读取正在更新的表：WHERE film_id IN (SELECT f.film_id FROM film f ...) 会报错“You can't specify target table 'film' for update in FROM clause”。请像上面那样，让子查询只读取其他表。

MySQL 和 MariaDB 还支持带 JOIN 的多表 UPDATE：

UPDATE film AS f
INNER JOIN film_category AS fc ON fc.film_id = f.film_id
INNER JOIN category AS c ON c.category_id = fc.category_id
SET f.rental_rate = f.rental_rate * 1.10
WHERE c.name = 'Comedy';

PostgreSQL 使用的是 UPDATE film SET ... FROM film_category, category WHERE ...；SQL Server 使用 UPDATE f SET ... FROM film f JOIN ...。

把值设为 NULL

UPDATE film
SET original_language_id = NULL
WHERE film_id = 10;

注意区别：SET column = NULL 赋值为“没有值”，而 WHERE column = NULL 不会匹配任何行；在条件中请使用 IS NULL。

更新前先检查

用相同的 WHERE 执行 SELECT，检查会涉及哪些行以及行数。
然后执行 UPDATE，并把“受影响的行数”与 SELECT 的结果进行比较。

-- 1. 哪些行会被修改？
SELECT film_id, title, rental_rate
FROM film
WHERE rating = 'NC-17';

-- 2. 只修改这些行
UPDATE film
SET rental_rate = rental_rate + 1.00
WHERE rating = 'NC-17';

在生产环境中，请在事务中执行重要的更新，以便出错时可以回滚。MySQL Workbench 和 MySQL 客户端还有一种“safe updates”模式，会拒绝没有键条件的 UPDATE 和 DELETE。

要点：

UPDATE table SET column = value WHERE condition 修改已有的行。
没有 WHERE 时，表中的每一行都会被修改。
SET 可以同时为多列赋值，并且可以使用它们的当前值。
要根据另一张表更新，请在 WHERE 中使用子查询或使用多表 UPDATE；在 MySQL 中，子查询不能读取正在更新的表。
先用 SELECT 检查行，并比较受影响的行数。

常见问题

如果执行不带 WHERE 的 UPDATE 会怎样？

表中的每一行都会被更新。唯一的保护措施是尚未提交的事务、safe updates 模式或备份。执行 UPDATE 之前，请务必检查 WHERE 子句。

如何在 SQL 中更新多列？

在 SET 中列出它们，用逗号分隔：UPDATE customer SET first_name = 'ALICE', last_name = 'COOPER' WHERE customer_id = 42;。

如何用另一张表中的值更新一张表？

在 WHERE 或 SET 中使用子查询，或者使用多表更新。在 MySQL 中：UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col WHERE ...；在 PostgreSQL 中：UPDATE t1 SET col = t2.col FROM t2 WHERE t1.id = t2.id。