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

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