如何用单条UPDATE语句更新关联表外键以清理重复数据？

需求背景 #

Table1中Val1、Val2、Val3组合为唯一编码，因自动导入流程产生重复数据。需先将Table2中关联的Table1.Id更新为每组重复数据的最大ID，再删除Table1中的重复记录。现有测试查询无法通用，需可批量处理的单条UPDATE语句。

表结构示例 #

Table1 #

Table2 #

现有测试SQL #

update Table2
set Table1.Id = (
    select ID
    from Table1
    where ID in (
        select max(ID)  
        from Table1
        group by ItemId, Va1, Val2, Val3
        having count(*) > 1
    )
    --and ItemId = 2  --added for testing
)
where Table1.ID in (
    select id
    from Table1
    where id not in (
        select max(id)
        from Table1
        group by ItemId, Va1, Val2, Val3
    )
    --and ItemId = 2  --added for testing
)

通用UPDATE语句方案 #

以下语句可批量将Table2中关联的重复Table1.Id更新为对应组的最大ID：

UPDATE t2
SET t2.[Table1.Id] = t1_max.max_id
FROM Table2 t2
JOIN Table1 t1 ON t2.[Table1.Id] = t1.ID
JOIN (
    SELECT ItemId, Val1, Val2, Val3, MAX(ID) AS max_id
    FROM Table1
    GROUP BY ItemId, Val1, Val2, Val3
) t1_max ON t1.ItemId = t1_max.ItemId 
        AND t1.Val1 = t1_max.Val1 
        AND t1.Val2 = t1_max.Val2 
        AND t1.Val3 = t1_max.Val3
WHERE t1.ID <> t1_max.max_id;

逻辑说明 #

1. 子查询t1_max按ItemId、Val1、Val2、Val3分组，计算每组的最大ID
2. 将Table2与Table1关联，再关联到t1_max，匹配每条Table2记录对应的组最大ID
3. 仅更新那些Table1.ID不等于组最大ID的记录，精准修改重复数据的关联关系

后续删除Table1重复记录 #

完成Table2的更新后，可执行以下语句删除Table1中的重复记录（保留每组最大ID的记录）：

DELETE FROM Table1
WHERE ID NOT IN (
    SELECT MAX(ID)
    FROM Table1
    GROUP BY ItemId, Val1, Val2, Val3
);

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