如何在PostgreSQL中通过查询另一张表更新整张表？

PostgreSQL里可以用UPDATE ... FROM语法或者子查询实现从另一张表更新目标大表，针对大量列的场景，有几种高效写法：

1. 基础UPDATE FROM语法（推荐） #

当两张表有明确关联键（比如id）时，这是最直接的批量更新方式：

UPDATE main_table m
SET (col1, col2, col3, ..., colN) = (u.col1, u.col2, u.col3, ..., u.colN)
FROM update_table u
WHERE m.id = u.id;

如果两张表列名完全一致（除关联键外），可以用行类型简化，不用手动罗列所有字段：

UPDATE main_table m
SET (m.*) = (u.*)
FROM update_table u
WHERE m.id = u.id;

⚠️ 注意：这种写法会更新包括关联键在内的所有列，若不想更新关联键，需手动指定要更新的列。

2. 带EXISTS的子查询写法 #

如果只想更新在更新表中有匹配记录的行，避免无匹配行被设为NULL，可以用这种写法：

UPDATE main_table m
SET (col1, col2, ..., colN) = (
    SELECT col1, col2, ..., colN 
    FROM update_table u 
    WHERE u.id = m.id
)
WHERE EXISTS (
    SELECT 1 
    FROM update_table u 
    WHERE u.id = m.id
);

关键注意事项 #

• 验证关联条件：执行前一定要用SELECT确认匹配数据，比如：SELECT m.id, u.id FROM main_table m JOIN update_table u ON m.id = u.id LIMIT 10;，避免错误更新。
• 大表分批更新：若目标表数据量极大，一次性更新会长期锁表，可按关联键分段执行，比如：

  UPDATE main_table m
  SET (col1, col2) = (u.col1, u.col2)
  FROM update_table u
  WHERE m.id = u.id AND m.id BETWEEN 1 AND 10000;
  

  多次执行不同范围直到完成更新。
• 处理重复关联键：如果update_table存在重复关联键记录，PostgreSQL会随机取一条更新，执行前需用DISTINCT ON或分组聚合去重。
• 事务保护：更新建议包裹在事务中，出错可回滚：

  BEGIN;
  -- 执行更新语句
  COMMIT;
  

内容的提问来源于stack exchange，提问作者Awesome