PostgreSQL中基于另一表最高record值更新表的实现

核心思路 #

PostgreSQL支持行记录批量赋值，结合获取每个ID最新记录的子查询，就能不用逐个列名写实现批量更新。

步骤1：获取每个ID的最新记录 #

用PostgreSQL专属的DISTINCT ON语法，简洁高效地拿到每个ID对应record值最大的那条记录：

SELECT DISTINCT ON (id) *
FROM table1
ORDER BY id, record DESC;

• DISTINCT ON (id)会按id分组，只保留每组的第一条记录
• ORDER BY id, record DESC确保每组里record最大的那条排在最前面，也就是最终被选中的记录

如果需要兼容其他数据库，也可以用窗口函数ROW_NUMBER()实现：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY record DESC) AS rn
    FROM table1
) t
WHERE rn = 1;

步骤2：批量更新Table2 #

利用行类型赋值+EXCLUDE子句（PostgreSQL 12+支持），批量更新所有字段，同时排除不需要更新的列（比如id主键、record版本字段）：

UPDATE table2 t2
SET t2 = t1.* EXCLUDE (id, record)
FROM (
    SELECT DISTINCT ON (id) *
    FROM table1
    ORDER BY id, record DESC
) t1
WHERE t2.id = t1.id;

关键注意事项 #

• 确保table2和table1中需要更新的字段名称、数据类型完全一致，否则会触发类型不匹配错误
• WHERE t2.id = t1.id必须加，否则会把table2中没有匹配id的行全部更新为NULL
• 如果你的PostgreSQL版本低于12，不支持EXCLUDE，可以用显式列列表的方式（列名太多时，可在psql里用\d table1导出字段列表批量整理）：

  UPDATE table2 t2
  SET (data1, data2, data3) = (t1.data1, t1.data2, t1.data3)
  FROM (...) t1
  WHERE t2.id = t1.id;
  

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