Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete duplicated rows and Update references

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.

Example:

Assume we have two tables Doubles and Data.

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

Data Table (
    Id int,
    DoublesId int
)

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 

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

like image 351
Thomas Avatar asked Sep 30 '09 10:09

Thomas


People also ask

How do I remove duplicate references?

Duplicate references are highlighted. To delete all the automatically detected duplicates, click on one of the highlighted references, hold down the left mouse button and drag across to the Trash folder. Alternatively you can right-click on any highlighted reference and select Move references to trash.

How do I remove duplicate rows in data migration?

Unique values are labeled with row number 1, while duplicates are 2, 3, and so on. Therefore, to remove duplicate rows, you need to delete everything except the ones marked with 1. This is done by running a DELETE query with the row_number as the filter.


1 Answers

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
)
like image 111
Robin Avatar answered Oct 24 '22 23:10

Robin