database - 更新 postgres 表以压缩第二个表中的重复值

Question

我有一个包含两个表的 postgresql 模式:

tableA:                      tableB:

| id | username |            | fk_id | resource |
| 1  | user1    |            | 2     | item1    |
| 2  | user1    |            | 1     | item3    | 
| 3  | user1    |            | 1     | item2    |
| 4  | user2    |            | 4     | item5    |
| 5  | user2    |            | 5     | item8    |
| 6  | user3    |            | 3     | item9    |

表B中的外键fk_id引用了表A中的id

如何更新表 B 的所有外键 ID 以指向表 A 中唯一用户名的最低条目？

Answer 1

update table_b b
set fk_id = d.id
from table_a a
join (
    select distinct on (username) username, id
    from table_a
    order by 1, 2
    ) d using(username)
where a.id = b.fk_id;

Test it here.

更新中使用的查询给出 actual_id、username、desired_id:

select a.id actual_id, username, d.id desired_id
from table_a a
join (
    select distinct on (username) username, id
    from table_a
    order by 1, 2
    ) d using(username)

 actual_id | username | desired_id 
-----------+----------+------------
         1 | user1    |          1
         2 | user1    |          1
         3 | user1    |          1
         4 | user2    |          4
         5 | user2    |          4
         6 | user3    |          6
(6 rows)