跳到主要内容

PostgreSQL UPDATE 实用教程（常用语法 + 安全更新 + 易错点）

UPDATE 基本语法​

UPDATE 表名
SET 列1 = 新值, 列2 = 新值
WHERE 行过滤条件
RETURNING 更新后要返回的字段;

**最常见写法：**用 WHERE 指定要修改的行，用 SET 指定要修改的列。

-- 把 id 为 100 的用户改名并更新状态
UPDATE users SET name = '小明', status = 1 WHERE id = 100;
信息

UPDATE 只修改符合条件的行，不会自动插入不存在的行。没有匹配行时，语句通常成功执行，但更新行数为 0。

WHERE 是安全更新的关键​

注意
高危：UPDATE 不写 WHERE，会更新表中的每一行！

生产环境执行前，先用同一组条件运行 SELECT 检查目标数据；条件不明确时不要执行更新。

-- 第一步：先确认将要更新的行
SELECT id, status FROM users WHERE status = 0 AND create_time < TIMESTAMP '2025-01-01 00:00:00';
-- 确认结果后再更新
UPDATE users SET status = 2 WHERE status = 0 AND create_time < TIMESTAMP '2025-01-01 00:00:00';

也可以在事务中先检查、再更新，确认结果后提交；发现不对就回滚。

BEGIN;
UPDATE users SET status = 2 WHERE id = 100 RETURNING id, status;
-- 确认返回结果正确后执行
COMMIT；否则执行 ROLLBACK COMMIT;
注意

不要把示例中的 COMMIT 不加判断地直接用于生产操作。事务要在同一数据库连接中完成，并在确认结果后决定提交或回滚。

基于原值计算更新​

SET 表达式可以使用该行更新前的列值，适合计数、累加和状态变更。

-- 库存减 1，同时累计销量
UPDATE products SET stock = stock - 1, sold_count = sold_count + 1 WHERE id = 10 AND stock > 0;
-- 基于原有金额加价
UPDATE products SET price = price * 1.05 WHERE category = 'book';
信息

WHERE stock > 0 把库存检查和扣减放在同一条语句中，可避免先查库存、再更新之间的竞态窗口。执行后仍应检查实际更新行数。

注意

同一条语句中的赋值都根据更新前的行值计算。例如 SET a = b, b = a 会交换 a、b 的旧值，而不是按从左到右的顺序执行赋值。

NULL、默认值与类型​

-- 将备注设为空值：使用 NULL，不要写字符串 'NULL'
UPDATE users SET remark = NULL WHERE id = 100;
-- 使用列的默认值
UPDATE users SET status = DEFAULT WHERE id = 100;
-- 显式转换类型（例如将文本转换为整数）
UPDATE products SET stock = '20'::integer WHERE id = 10;
注意

NULL 表示缺失或未知值，不等于字符串 'NULL'，也不能用 = NULL 判断。筛选空值请用 IS NULL。

如果列设置了约束（例如 NOT NULL、CHECK 或外键），新值必须满足约束，否则整条语句会报错。

UPDATE ... FROM：关联其他表更新​

PostgreSQL 可以通过 FROM 关联来源表，为目标表写入查询到的值。

-- 将每个用户最近订单的金额写入用户表
UPDATE users AS u SET last_order_amount = o.amount FROM orders AS o WHERE o.user_id = u.id AND o.is_latest = true;
注意
易错：每一条目标记录应最多匹配一条来源记录。

如果一个用户匹配到多条订单，PostgreSQL 只会使用其中一条来源行，但具体使用哪一条不确定。应先保证关联键唯一，或先在子查询中聚合/选出唯一行。

-- 先按用户聚合，保证每个用户最多匹配一条汇总结果
UPDATE users AS u SET total_order_amount = summary.total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) AS summary WHERE summary.user_id = u.id;

目标表的列写在 SET 左侧时不要加表别名限定，例如写 SET status = 1，而不是 SET u.status = 1。

子查询更新​

用子查询计算要写入的值。需要确保子查询最多返回一行；返回多行会报错。

-- 根据用户 id 查找其部门名称并更新
UPDATE users AS u SET department_name = ( SELECT d.name FROM departments AS d WHERE d.id = u.department_id ) WHERE u.department_id IS NOT NULL;
信息

如果子查询没有匹配结果，标量子查询会得到 NULL。如需保留原值或跳过没有匹配项的行，请在 WHERE 中明确限制匹配范围。

RETURNING：直接查看更新结果​

PostgreSQL 支持在更新后返回受影响行的数据，适合确认更新内容，也可供应用程序获取新值。

-- 返回更新后的字段
UPDATE users SET status = 1 WHERE id = 100 RETURNING id, name, status;
-- RETURNING * 会返回更新后的整行
UPDATE products SET price = price * 1.05 WHERE id = 10 RETURNING *;
注意

RETURNING 返回的是语句实际更新的行，不是“准备更新”的行。没有匹配数据时返回空结果集。

UPDATE 与并发、事务​

  • UPDATE 会对被修改的行加锁；其他事务更新同一行时可能等待。
  • 事务中发生错误后，当前事务通常需要回滚，不能直接假定后续语句会继续成功。
  • 批量更新可能持有较多行锁、产生大量日志；大表操作应评估影响并分批执行。
  • 检查客户端或驱动报告的受影响行数，避免“执行成功但更新了 0 行”被误认为更新成功。
-- 按主键范围分批更新的示意：每批单独执行并确认行数
UPDATE events SET archived = true WHERE id > 0 AND id <= 10000 AND archived = false;
信息

分批边界要根据表结构和业务设计确定；需保证后续批次能继续覆盖所有目标数据，并避免跳过或重复处理。

常见需求：不存在则插入​

UPDATE 不会在目标行不存在时自动插入。若需求是“有则更新，无则插入”，使用 PostgreSQL 的 INSERT ... ON CONFLICT（UPSERT）。

-- user_id 上需要有唯一约束或唯一索引
INSERT INTO user_settings (user_id, theme) VALUES (100, 'dark') ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme RETURNING user_id, theme;
注意

ON CONFLICT (user_id) 依赖 user_id 上存在匹配的唯一约束或唯一索引；否则语句会报错。

UPDATE 高频易错清单​

  • 1. 忘记 WHERE 会更新全表；执行前用相同条件 SELECT 检查。
  • 2. 更新零行也可能不报错；要检查受影响行数或 RETURNING 结果。
  • 3. NULL 是 SQL 空值，写 NULL 而不是字符串 'NULL'，判断用 IS NULL。
  • 4. UPDATE ... FROM 的来源结果应对每条目标行最多匹配一行。
  • 5. SET 中各表达式基于更新前的列值求值，不是按书写顺序逐项更新。
  • 6. UPDATE 不会插入不存在的数据；需要插入或更新时用 INSERT ... ON CONFLICT。
  • 7. 生产批量更新先评估事务、锁、行数和回滚方案。