MySQL - Duplicate elimination and Preserving Valuable Data?

Question

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 :)

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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