SQL 过程 : Removing duplicates, 重新分配外键引用

Question

我在一家处理排名比赛的公司工作。

不幸的是，他们的成员(member)表对电子邮件没有唯一的限制，并且一些用户一直在为他们参加的每个比赛或团队创建一个使用相同电子邮件的新帐户。

我想对列施加唯一约束以防止将来出现任何重复，但是...

问题:如何使用单个查询删除重复项而不丢失与其连接的数据？

我认为这与更新所有外键以匹配用户的一个实例然后删除重复项有关。

澄清:在下面的示例中，标记的行引用 ID 为 03、04、05 和 06 的重复成员。在这种情况下，解决方案是:

1. ID 为 03 和 05 的外键引用已更改为 01。
2. ID 为 04 和 06 的外键引用更改为 02。
3. 已删除 ID 为 03、04、05 和 06 的重复成员。

但是在 MSSQL 中如何做到这一点呢？

Member table
ID | Username | Gender | Email
01 | User1    | Male   | fake@fu.bar
02 | User2    | Female | alsofake@fu.bar
*03 | User3    | Male   | fake@fu.bar
*04 | User4    | Female | alsofake@fu.bar
*05 | User5    | Male   | fake@fu.bar
*06 | User6    | Female | alsofake@fu.bar


MemberToTeam table
MemberID_fk | TeamID_fk
01          | 01
02          | 01
*03          | 02
*04          | 02
*05          | 03
*06          | 03

RaceRank table
RaceID_fk | MemberID_fk | Ranking
01        | 01          | 12
01        | 02          | 1
*02        | 03          | 5
*02        | 04          | 7
*03        | 05          | 4
*03        | 06          | 9

感谢您的帮助。

Answer 1

这在一个查询中完成。对另一个表重复此操作。

with FAKES as
(
select Email
from Member
group by Email
having count(id) >1
),
FAKE_ID as
(
select id, email, row_number() over(partition by email order by id) as c_id
from Member
where email in (select Email from FAKES)
)
,
DEDUP as
(
select fi.id, f2.id as val_id
from FAKE_ID fi
inner join FAKE_ID f2
  on fi.email = f2.email
where fi.c_id > 1
and f2.c_id = 1
)
update mt
set mt.MemberID_fk = dd.val_id
from MemberToTeam mt
inner join DEDUP dd
on dd.id = mt.MemberID_fk;

已测试here