SQL:如何在单独的表中更新重复项？

Question

我有 2 个表:

Table1:    
id1 | id2
1   | a
2   | a
3   | a
4   | b
5   | b

Table2:    
data | id1
...  | 1
...  | 2
...  | 2
...  | 3
...  | 4
...  | 5

在 Table1 中，我应该具有 id1->id2 的唯一关联，但由于某些未知原因，它不是。我需要修复它并添加唯一约束。我只需要在 Table1 中保留一对一关系，并仅使用 Table1 中保留的那个 ID 更新 Table2 中的重复 ID。结果我应该有:

Table1:    
id1 | id2
1   | a
4   | b

Table2:    
data | id1
...  | 1
...  | 1
...  | 1
...  | 1
...  | 4
...  | 4

我知道如何找到重复的 ID:

SELECT id1 FROM Table1 GROUP BY id2 HAVING COUNT(id2) > 1;

但我对如何进行下一次更新和删除有点迷茫。

id1和id2的数据类型是UUID。

Answer 1

将问题视为保持第一 关系。那么，删除就没那么难了:

delete from table1
     where table1.id1 > (select min(tt1.id1) from table1 tt1 where tt1.id2 = table1.id2);

现在，为了修复表 2，我们需要一个更复杂的查询来保存由此产生的结果。幸运的是，Postgres 允许 CTE 包含数据修改步骤:

with todelete as (
      select t1.*, min(t1.id1) over (partition by id2) as keepid
      from table1
     ),
     d as (
      delete from table1   
      where table1.id > (select min(tt1.id) from table1 tt1 where tt1.id2 = table1.id2)
    )
update table2
    set id1 = (select keepid from todelete where todelete.id1 = table2.id2);