sql - 如何更新具有重复 ID 的引用表？

Question

我正在使用 postgresql。要删除表中的重复项，我使用此查询:

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

引用:https://stackoverflow.com/a/12963112/4940278

但是，有一个表说ref_table，其中也引用了dups.id。在删除重复项之前，我需要更新另一个表。用重复的 ID 更新引用表的查询是什么，这样就不会丢失数据？

例如:

表1，说dups

id key
1  Luna
2  Hermione
3  Luna

表2，说ref_table

id dups_id data
1   2     Auror
2   1     Researcher

现在删除重复项的查询将删除 dups 表中 ID 为 1 的记录，因为它是重复项。然而，该记录在 ref_table 中被引用，所以我需要用将要保留的记录更新它。

即)Table1 应该变成:

id key
2  Hermione
3  Luna

表 2 应该变成:

id dups_id data
1   2     Auror
2   3     Researcher

Answer 1

使用 CTE 来识别 dups 中维护的行，然后更新 ref 行，以便 FK 仅指向它们，最后删除不再需要的行。

with keeper as                                     -- identify dups rows to be kept
     ( select id, key
            , max(id) over(partition by key) mid 
         from dups)

   , u as                                          -- update ref so dup_id references only those being kept
     ( update ref r 
        set dup_id =  (select k.mid 
                         from keeper k
                         join dups   d 
                           on (k.id=d.id)
                        where r.dup_id != k.mid
                          and r.dup_id = k.id
                      )
     )   

delete                                             -- final target remove dups rows no lnger needed
  from dups d                                            
 where d.id not in (select mid from keeper);