Deleting Duplicate Records in MySQL While Preserving the Latest

In a database, it's common to encounter duplicate records, particularly in tables with unique identifiers. In MySQL, you may face a situation where emails get duplicated, and you desire to retain only the latest one with the most recent ID.

To solve this problem, we can employ the following steps:

1. Identify Duplicate Emails: Determine the emails that appear more than once in the table.
2. Find the Latest ID for Each Duplicate Email: For each duplicate email, retrieve the highest ID, which represents the most recent insertion.
3. Delete Duplicates with Older IDs: Remove all duplicates except the latest ones identified in the previous step.

Implementation:

Consider the following MySQL table named test with columns id and email:

| id | email |
|---|---|
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | bbb |
| 5 | ddd |
| 6 | eee |
| 7 | aaa |
| 8 | aaa |
| 9 | eee |

To delete duplicate emails and keep the latest ones, we can execute the following query:

DELETE test
FROM test
INNER JOIN (
  SELECT MAX(id) AS lastId, email
  FROM test
  GROUP BY email
  HAVING COUNT(*) > 1
) duplic ON duplic.email = test.email
WHERE test.id 
This query retrieves the latest IDs for duplicate emails and removes all duplicates with older IDs. After executing the query, the test table will appear as follows:
| id | email |
|---|---|
| 3 | ccc |
| 4 | bbb |
| 5 | ddd |
| 8 | aaa |
| 9 | eee |
Only the latest duplicates with the highest IDs have been preserved, satisfying the requirement to maintain the most recent email addresses in the table.