26 UPDATE

UPDATE 会修改已有行。基本形式是 UPDATE table SET column = value WHERE condition。没有 WHERE 子句时，表中的每一行都会被更新——这种错误只能在自己的练习数据库里犯。始终先用 SELECT 检查，并考虑在确认无误前将有风险的更新包在 BEGIN ... ROLLBACK 中。

本章要点

• 一次更新一列、多列和多行
• 使用 WHERE 限定更新范围
• 使用 UPDATE ... FROM 根据另一个表的数据更新一个表
• 使用 RETURNING 返回修改后的行
• UPDATE 的事务安全性（以及如何滥用它）

示例

-- Single row, single column
UPDATE fruits SET name = 'apricot' WHERE id = 1;

-- Update another row
UPDATE fruits SET name = 'blueberry' WHERE id = 2;

-- Expression-based update: bump every id by 1000
-- (commented out so the rest of the lessons can find the original ids)
-- UPDATE fruits SET id = id + 1000;

-- UPDATE ... FROM: use another table as the source of values
DROP TABLE IF EXISTS price_changes_example;
CREATE TABLE price_changes_example (id integer, new_name text);
INSERT INTO price_changes_example VALUES (3, 'chestnut'), (4, 'damson');

UPDATE fruits AS f
SET    name = pc.new_name
FROM   price_changes_example pc
WHERE  f.id = pc.id;

DROP TABLE price_changes_example;

-- Returning the rows that changed
UPDATE fruits
SET    name = name        -- no-op, just for the demo
WHERE  id = 5
RETURNING id, name;

SELECT * FROM fruits ORDER BY id LIMIT 5;

UPDATE 完全受事务保护：在你 COMMIT 之前（取决于隔离级别），其他会话看不到你的修改，而 ROLLBACK 会撤销这些修改。SELECT 的同样 WHERE 规则也适用。

UPDATE ... FROM 是 PostgreSQL 提供的扩展，允许你使用另一个表的数据更新一个表中的行。FROM 子句引入其他表，而 WHERE 子句负责将它们连接起来。这通常比在 SET 列表中嵌套关联子查询更清晰（也更快）。

psql 的状态消息 UPDATE n 会告诉你修改了多少行。UPDATE 0（零行）通常说明 WHERE 没有匹配到任何行——这对发现应用代码中的拼写错误或过时假设很有用。

运行示例

运行方式（需要前面课程创建的 fruits）：

$ psql -f source/update.sql postgres
UPDATE 1
UPDATE 1
CREATE TABLE
INSERT 0 2
UPDATE 2
DROP TABLE
 id |    name
----+------------
  5 | elderberry
(1 row)
UPDATE 1
 id |    name
----+------------
  1 | apricot
  2 | blueberry
  3 | chestnut
  4 | damson
  5 | elderberry
(5 rows)

常见陷阱

• 忘记 WHERE 而更新整张表——没有备份或事务时无法撤销。拿不准时，先执行 BEGIN;，运行 UPDATE，检查行数，再执行 COMMIT; 或 ROLLBACK;。
• 更新被外键引用的主键：外键的 ON UPDATE 动作会决定这次变更是向外传播、导致失败，还是将值设为 NULL。
• 将列更新为它已有的值，仍然会重写该行、获取锁，并触发任何 BEFORE UPDATE 触发器。如果这点很重要，请添加 WHERE col IS DISTINCT FROM new_value，以进行能正确处理 NULL 的比较。
• 在繁忙表上长时间运行的 UPDATE 会持有行锁；并发更新会等待。将大批量更新拆分成较小的事务。

下载

SQL 文件：update.sql · 全部示例文件 · 准备工作