How to delete duplicate records in MySQL by retaining those fields with data in the duplicate item b
- by NJTechGuy
I have few thousands of records with few 100 fields in a MySQL Table.
Some records are duplicates and are marked as such. Now while I can simply delete the dupes, I want to retain any other possible valuable non-null data which is not present in the original version of the record. Hope I made sense.
For instance :
a b c d e f key dupe
--------------------
1 d c f k l 1 x
2 g h j 1
3 i h u u 2
4 u r t 2 x
From the above sample table, the desired output is :
a b c d e f key dupe
--------------------
2 g c h k j 1
3 i r h u u 2
If you look at it closely, the duplicate is determined by using the key (it is the same for 2 records, so the one that has an 'x' for dupe field is the one to be deleted by retaining some of the fields from the dupe (like c, e values for key 1).
Please let me know if you need more info about this puzzling problem.
Thanks a tonne!
p.s : If it is not possible using MySQL, a PERL/Python script sample would be awesome! Thanks!
