PostgreSQL不支持UPDATE JOIN语法，正确写法是UPDATE...FROM...WHERE，目标表不能出现在FROM中，WHERE必须含明确关联条件且建议含主键以避免误更新。
PostgreSQL 不支持标准 SQL 的多表 UPDATE 语法
PostgreSQL 原生不接受 UPDATE ... FROM ... JOIN 这种 MySQL 或 SQL Server 风格的写法（虽然它有 FROM 子句，但语义不同）。直接写 UPDATE t1 SET x = t2.y FROM t2 WHERE t1.id = t2.t1_id 看似可行，但必须严格满足 PostgreSQL 的 FROM 规则：被更新的表不能在 FROM 中重复出现，且关联条件必须能明确绑定到目标行。稍有不慎就会报错 table name "t1" specified more than once 或更新行为不符合预期。
正确写法：用 FROM 子句关联子查询或别名表
PostgreSQL 允许在 UPDATE 的 FROM 中引用其他表（或子查询），但目标表本身不能出现在 FROM 列表里。关键在于让 FROM 提供的是「只读上下文」，再通过 WHERE 或 SET 中的表达式引用其字段。
- 用显式别名避免歧义：
UPDATE orders SET status = 'shipped' FROM customers c WHERE orders.customer_id = c.id AND c.country = 'CN' - 多表关联需嵌套子查询或 CTE，例如更新订单状态为“VIP已发货”仅当客户是 VIP 且存在物流记录：
UPDATE orders SET status = 'vip_shipped' FROM ( SELECT o.id FROM orders o JOIN customers c ON o.customer_id = c.id AND c.is_vip = true JOIN shipments s ON o.id = s.order_id ) AS matched WHERE orders.id = matched.id;
- 如果要更新的字段依赖多个表的计算结果，把逻辑放到
FROM的子查询里更清晰，比如：UPDATE products SET price = src.new_price FROM (SELECT id, base_price * (1 + COALESCE(discount_rate, 0)) AS new_price FROM products p LEFT JOIN promotions pr ON p.id = pr.product_id) AS src WHERE products.id = src.id
常见错误：WHERE 条件漏掉主键/唯一键约束
使用 FROM 关联后，若 WHERE 条件未精确匹配目标表的单行，PostgreSQL 可能静默地更新多行（甚至零行），而不会报错。这和 MySQL 的严格模式不同。
- 错误示范：
UPDATE logs SET processed = true FROM events e WHERE logs.event_type = e.type—— 若多个events行匹配同一logs行，PostgreSQL 仍会更新该logs行一次，但语义模糊，易引发数据不一致 - 安全做法：始终确保
WHERE中包含目标表的主键或唯一约束字段，例如WHERE logs.id = e.log_id或WHERE logs.id IN (SELECT log_id FROM events WHERE ...) - 不确定时先用
SELECT验证：SELECT l.id FROM logs l JOIN events e ON l.id = e.log_id WHERE ...看返回行数是否符合预期
替代方案：用 CTE 实现更可控的多表更新逻辑
当逻辑复杂、涉及聚合或需要多次引用中间结果时，CTE 比嵌套 FROM 更易读且不易出错。CTE 本身不改变执行计划，但结构上强制你分步思考。
- 例如同步更新用户积分并记录日志：
WITH calc AS ( SELECT u.id, u.points + COALESCE(o.total_amount * 10, 0) AS new_points FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid' AND o.created_at > '2024-01-01' ), log_data AS ( SELECT id, new_points FROM calc WHERE new_points > users.points ) UPDATE users SET points = log_data.new_points FROM log_data WHERE users.id = log_data.id;
- 注意：CTE 中的
UPDATE仍需遵守目标表不出现在FROM的规则；上面例子中users是目标表，没出现在FROM的任何位置 - CTE 不是物化视图，PostgreSQL 通常会将其内联优化，性能一般不输纯
FROM写法
真正容易被忽略的是：PostgreSQL 的 UPDATE ... FROM 在事务中不加锁其他表，除非显式 SELECT ... FOR UPDATE。如果并发场景下依赖另一张表的状态做更新，光靠 FROM 关联无法防止竞态——这时候得考虑应用层加锁或改用 SELECT ... FOR UPDATE THEN UPDATE 模式。
