PostgreSQL UPDATE关联更新时数据未正确更新问题

你遇到的是PostgreSQL UPDATE FROM 语法的典型使用错误：

• UPDATE关键字后声明的目标表和FROM子句内的表没有建立关联，导致目标表的每一行都会和FROM子句返回的结果集做笛卡尔积匹配，最终所有行都会被覆盖为第一个匹配到的s.number值
• 原语句还存在目标表声明错误：要更新的是public.Department表，但语句开头写的是UPDATE public.table，二者没有关联进一步加剧了全表误更新的问题

UPDATE public.Department AS a
SET number = s.number,
    updatedt = now()                
FROM public.Department_stg AS s
WHERE a.id = s.id 
  -- 兼容NULL值的不等判断，避免NULL值跳过更新
  AND COALESCE(CAST(a.number AS VARCHAR(20)), '') <> COALESCE(CAST(s.number AS VARCHAR(20)), '');

• PostgreSQL的UPDATE FROM语法要求必须通过WHERE子句将UPDATE后的目标表和FROM子句内的关联表绑定，否则就会出现全表匹配误更新的问题
• 数值比较时如果字段允许为NULL，不能直接用=或者<>判断，需要用COALESCE函数做值替换后再比较，否则NULL值的行不会被匹配到
• 执行UPDATE前建议先把UPDATE部分替换为SELECT查询验证匹配结果是否符合预期，避免误更新数据

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