I have unique id
and email
fields. Emails get duplicated. I only want to keep one Email address of all the duplicates but with the latest id
(the last inserted record).
How can I achieve this?
I have unique id
and email
fields. Emails get duplicated. I only want to keep one Email address of all the duplicates but with the latest id
(the last inserted record).
How can I achieve this?
Imagine your table
test
contains the following data:So, we need to find all repeated emails and delete all of them, but the latest id.
In this case,
aaa
,bbb
andeee
are repeated, so we want to delete IDs 1, 7, 2 and 6.To accomplish this, first we need to find all the repeated emails:
Then, from this dataset, we need to find the latest id for each one of these repeated emails:
Finally we can now delete all of these emails with an Id smaller than LASTID. So the solution is:
I don't have mySql installed on this machine right now, but should work
Update
The above delete works, but I found a more optimized version:
You can see that it deletes the oldest duplicates, i.e. 1, 7, 2, 6:
Another version, is the delete provived by Rene Limon
Try this method
I must say that the optimized version is one sweet, elegant piece of code, and it works like a charm even when the comparison is performed on a DATETIME column. This is what I used in my script, where I was searching for the latest contract end date for each EmployeeID:
Many thanks!
Correct way is