How to Delete Duplicate Records in MySQL While Keeping the Latest?

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 < duplic.lastId;

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.

The above is the detailed content of How to Delete Duplicate Records in MySQL While Keeping the Latest?. For more information, please follow other related articles on the PHP Chinese website!