PostgreSQL报错：同一查询不允许更新同一行多次，求修改方案

错误原因 #

你遇到的错误是因为stg表（t2）中存在多条与test表（t1）同一记录匹配的行（即同一个subject_id+wf_id+instance_id+session_id组合对应多条t2记录），PostgreSQL不允许同一条UPDATE语句多次更新目标表的同一行。

解决方案 #

根据t2中重复记录的字段值情况，选择以下方案：

情况1：t2中匹配的重复记录更新字段值完全相同 #

此时只需对t2按匹配键去重后再关联更新，两种实现方式：

方式1：用DISTINCT去重

UPDATE test t1 SET 
    folder=t2.folder,
    name=t2.name,
    session_name=t2.session_name,
    mapping_name=t2.mapping_name,
    update_date=now()::timestamp,
    is_deleted='N'
FROM (
    SELECT DISTINCT subject_id, wf_id, instance_id, session_id,
           folder, name, session_name, mapping_name
    FROM stg 
    WHERE insert_date IS NULL
) t2 
WHERE t1.subject_id=t2.subject_id 
  AND t1.wf_id=t2.wf_id 
  AND t1.instance_id=t2.instance_id 
  AND t1.session_id=t2.session_id;

注：将to_char(now(),'yyyy-mm-dd hh24:mi:ss')::timestamp简化为now()::timestamp，两者效果一致。

方式2：用GROUP BY聚合

因为字段值相同，用MAX/MIN聚合后结果不变：

UPDATE test t1 SET 
    folder=t2.folder,
    name=t2.name,
    session_name=t2.session_name,
    mapping_name=t2.mapping_name,
    update_date=now()::timestamp,
    is_deleted='N'
FROM (
    SELECT subject_id, wf_id, instance_id, session_id,
           MAX(folder) AS folder,
           MAX(name) AS name,
           MAX(session_name) AS session_name,
           MAX(mapping_name) AS mapping_name
    FROM stg 
    WHERE insert_date IS NULL
    GROUP BY subject_id, wf_id, instance_id, session_id
) t2 
WHERE t1.subject_id=t2.subject_id 
  AND t1.wf_id=t2.wf_id 
  AND t1.instance_id=t2.instance_id 
  AND t1.session_id=t2.session_id;

情况2：t2中匹配的重复记录更新字段值不同 #

此时需要指定取哪一条记录的数值更新，比如取最新的一条（需有时间字段）：

UPDATE test t1 SET 
    folder=t2.folder,
    name=t2.name,
    session_name=t2.session_name,
    mapping_name=t2.mapping_name,
    update_date=now()::timestamp,
    is_deleted='N'
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY subject_id, wf_id, instance_id, session_id 
               ORDER BY create_date DESC -- 替换为你的实际时间字段，用于筛选最新记录
           ) AS rn
    FROM stg 
    WHERE insert_date IS NULL
) t2 
WHERE t1.subject_id=t2.subject_id 
  AND t1.wf_id=t2.wf_id 
  AND t1.instance_id=t2.instance_id 
  AND t1.session_id=t2.session_id
  AND t2.rn=1; -- 只保留每组的第一条记录

注意事项 #

执行UPDATE前，建议先单独运行子查询（括号内的SQL），确认去重/筛选后的记录符合预期，避免误更新数据。

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