mysql delete duplicate rows but keep one 1

mysql delete duplicate rows but keep one

DELETE FROM CONTACTS
WHERE ID NOT IN
      (SELECT *
       FROM (SELECT max(ID)		
             FROM CONTACTS
             GROUP BY EMAIL) t);
 -- ⇓ Test it ⇓ (Fiddle source link)

Here is what the above code is Doing:
1. The innermost query is selecting the maximum ID for each email.
2. The next query is selecting all the rows from the table that have an ID that is in the result of the innermost query.
3. The outermost query is deleting all the rows from the table that are not in the result of the second query.

Similar Posts