时间戳

首页 > 解决方案 > 如何更新具有重复 ID 的引用表？

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

问题描述

我正在使用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，因此我需要使用将要保留的记录来更新它。

即）表1应该变成：

id key
2  Hermione
3  Luna

表 2 应变为：

id dups_id data
1   2     Auror
2   3     Researcher

标签： sqlpostgresqlpostgresql-9.4

解决方案

使用 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);

推荐阅读