PostgreSQL中如何用关联表的最高版本值更新目标表字段？

前置优化 #

因为表B数据量达2200万行，执行操作前请先确认表B已创建(ItemID, Version)联合索引，该索引可以直接快速检索每个ItemID对应的最大Version值，避免全表扫描，大幅提升执行效率。

第一步：数据校验（必做，避免误操作） #

先执行以下查询，核对不同步的条目是否符合预期：

SELECT 
  a.ID, 
  a.Total AS old_total, 
  MAX(b.Version) AS correct_total
FROM TableA a
LEFT JOIN TableB b ON a.ID = b.ItemID
GROUP BY a.ID, a.Total
HAVING a.Total <> MAX(b.Version);

第二步：执行更新操作 #

根据你使用的数据库选择对应SQL：

通用标准SQL（适配Oracle、SQL Server等多数数据库） #

UPDATE TableA a
SET Total = (
  SELECT MAX(b.Version)
  FROM TableB b
  WHERE b.ItemID = a.ID
)
WHERE EXISTS (
  SELECT 1
  FROM TableB b
  WHERE b.ItemID = a.ID
  HAVING MAX(b.Version) <> a.Total
);

MySQL 专属写法 #

UPDATE TableA a
INNER JOIN (
  SELECT ItemID, MAX(Version) AS max_ver
  FROM TableB
  GROUP BY ItemID
) b ON a.ID = b.ItemID
SET a.Total = b.max_ver
WHERE a.Total <> b.max_ver;

PostgreSQL 专属写法 #

UPDATE TableA a
SET Total = b.max_ver
FROM (
  SELECT ItemID, MAX(Version) AS max_ver
  FROM TableB
  GROUP BY ItemID
) b
WHERE a.ID = b.ItemID AND a.Total <> b.max_ver;

注意事项 #

• 操作前请先备份表A全量数据，出现异常可快速回滚
• 建议在业务低峰期执行操作，避免锁冲突影响线上业务
• 如果存在表A的ID在表B中无对应记录的场景，可通过COALESCE函数处理NULL值，比如将MAX(b.Version)替换为COALESCE(MAX(b.Version), 0)（无记录时设为0）或COALESCE(MAX(b.Version), a.Total)（无记录时保留原有值）

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