删除重复的行并更新引用 [英] Delete duplicated rows and Update references

问题描述

如何删除一个表中的重复行并将另一个表中的引用更新到剩余行?重复仅发生在名称中.Id 列是 Identity 列.

How do I Delete duplicated rows in one Table and update References in another table to the remaining row? The duplication only occurs in the name. The Id Columns are Identity columns.

假设我们有两个表 Double 和 Data.

Assume we have two tables Doubles and Data.

Doubles table (
   Id int,
   Name varchar(50)
)

Data Table (
    Id int,
    DoublesId int
)

现在我在 Doubls 表中有两个条目:

Now I Have Two entries in the Doubls table:

Id Name
1  Foo
2  Foo

以及数据表中的两个条目:

And two entries in the Data Table:

ID DoublesId
1  1
2  2

最后在双打表中应该只有一个条目:

At the end there should be only one entry in the Doubles Table:

Id Name
1  Foo

以及数据表中的两个条目:

And two entries in the Data Table:

Id DoublesId
1  1
2  1 

在双打表中，每个名称可以有任意数量的重复行(最多 30 行)以及常规的单"行.

In the doubles Table there can be any number of duplicated rows per name (up to 30) and also regular 'single' rows.

推荐答案

我没有运行过这个，但希望它应该是正确的，并且足够接近最终解决方案，让你到达那里.如果您愿意，请告诉我任何错误，我会更新答案.

I've not run this, but hopefully it should be correct, and close enough to the final soln to get you there. Let me know any mistakes if you like and I'll update the answer.

--updates the data table to the min ids for each name
update Data
set id = final_id
from
  Data
join
  Doubles 
on Doubles.id = Data.id
join
(
  select 
    name
    min(id) as final_id
  from Doubles
  group by name
) min_ids
on min_ids.name = Doubles.name

--deletes redundant ids from the Doubles table
delete 
from Doubles
where id not in
(
  select 
    min(id) as final_id
  from Doubles
  group by name
)

这篇关于删除重复的行并更新引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！