CODEWITHSUNDEEP
mysqlduplicates
ThinkCode
ThinkCode

Reputation: 7961

MySQL - Duplicate elimination and Preserving Valuable Data?

Scenario : I have few duplicate contacts in a table. The duplicates are identified, I can just delete them but the problem is I don't want to lose the data the duplicate might have and the original don't. Any tips?

Sample data : 

ID Name Email School Dupe_Flag Key
1  AAA  a@a          X         1 
2  AAB        JKL              1
3  BBB  b@b   MNO    X         2
4  BBC                         2

Desired output :

ID Name Email School Dupe_Flag Key
1  AAA  a@a          X         1 
2  AAB  a@a   JKL              1
3  BBB  b@b   MNO    X         2
4  BBC  b@b   MNO              2

How are 2 records related? : They both have the same Key Value with only one column having the Dupe_Flag SET which is the duplicate column.

In the above case ID 1 is going to be deleted but email info from ID 1 should be applied to ID 2.

What is the Data? : I have few hundred rows and few 100 duplicates. UPDATE statement for each row is cumbersome and is not feasible.

Business rules for determining what data takes priority :

If a column from the original/good record (Dupe_Flag is NOT set) has no data and if the corresponding Dupe record (has the same Key value) column has data then that original record column should be updated.

Any help/script is really appreciated! Thanks guys :)

Upvotes: 7

Views: 395

Answers (4)

Benoît Vidis
Benoît Vidis

Reputation: 3902

Assuming empty values are null, something like this should output the desired data:

SELECT
  a.ID,
  IF(a.DupeFlag IS NULL, IF(a.Name IS NULL, b.Name, a.Name), a.Name) AS Name,
  IF(a.DupeFlag IS NULL, IF(a.Email IS NULL, b.Email, a.Email), a.Email) AS Email,
  IF(a.DupeFlag IS NULL, IF(a.School IS NULL, b.School, a.School), a.School) as School,
  a.DupeFlag,
  a.key
FROM
  table a,
  table b
WHERE
  a.Key = b.Key AND
  a.ID != b.ID
GROUP BY
  a.ID

Note that turning this in an UPDATE statement is pretty straight-forward

Upvotes: 2

furrymitn
furrymitn

Reputation: 51

While this uses a bunch of nested SELECTS, and isn't really a full solution, it should either spark something else, or possibly push in the right direction.

select * from 
  (select r1.ID,r1.Name,coalesce(r1.Email,r2.Email) as Email,
  coalesce(r1.School,r2.School) as School,r1.Dupe_Flag,r1.Key from 
  (select * from test1 where Dupe_Flag IS NULL) as r1  left outer join 
  (select * from test1 where Dupe_Flag IS NOT NULL) as r2 on r1.KEY=r2.Key) 
as results

Yields:

ID  Name  Email  School  Dupe_Flag  Key
2   AAB   a@a    JKL     NULL       1
4   BBC   b@b    MNO     NULL       2

Based on your example data.

Upvotes: 0

pete
pete

Reputation: 1

The rows are unique, so there's no problem. Please recheck your example data.

Upvotes: -1

rook
rook

Reputation: 67019

I don't know the specifics of this problem but it is probably better to avoid this problem by setting the columns to "unique" so if a query tries to create a duplicate it will fail. I think the elegant solution to this problem is to avoid it at the point of data entry.

I like using this query for tracking down dupes:

select * from table group by `Email` having count(Email) > 1

Upvotes: 0

Related Questions