PostgreSQL更新OptionsData表重复记录遇列d1不存在错误求助

先说说我理解的你的场景：在清理public."OptionsData"表时，发现asofdate='2020-08-04'的同一合约存在两条重复记录，示例数据如下：

你的需求很明确：保留id较小的那条记录的asofdate不变，把id更大的那条的asofdate改成'2020-08-05'。

你之前SQL报错的原因 #

PostgreSQL的UPDATE语法里，表别名不能用在SET子句的列前面（比如你写的d1.asofdate是错误的），列名直接写就可以，别名只在WHERE子句和关联的子查询里生效。

两种可行的解决方案 #

方案一：修正原有的EXISTS查询

只需要去掉SET里的表别名，同时确保子查询的关联逻辑正确即可：

UPDATE public."OptionsData" d1
SET asofdate = '2020-08-05'  -- 这里不用加d1.前缀
WHERE asofdate = '2020-08-04'
  AND EXISTS (
    SELECT 1
    FROM public."OptionsData" d2
    WHERE d2.asofdate = d1.asofdate
      AND d2.contract = d1.contract
      AND d2.id < d1.id
  );

这个查询的逻辑是：找到所有asofdate='2020-08-04'的记录，并且判断该记录是否存在同一asofdate和contract下id更小的记录，如果存在，就把这条记录的日期改成目标值。

方案二：用窗口函数处理（更灵活）

如果以后遇到同一合约下有更多重复记录的情况，用窗口函数会更直观，也能一次性处理所有重复行：

WITH ranked_data AS (
  SELECT 
    id,
    -- 按asofdate和contract分组，每组内按id升序排，给每条记录一个序号
    ROW_NUMBER() OVER (PARTITION BY asofdate, contract ORDER BY id) AS rn
  FROM public."OptionsData"
  WHERE asofdate = '2020-08-04'
)
UPDATE public."OptionsData"
SET asofdate = '2020-08-05'
FROM ranked_data
WHERE public."OptionsData".id = ranked_data.id
  AND ranked_data.rn > 1;  -- 序号>1的就是每组里id较大的重复行

这种方式通过ROW_NUMBER()给每组重复记录排序，序号为1的是id最小的那条（保留原日期），序号大于1的都是需要更新的行，扩展性更强。

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