How Can I Delete Duplicate Records in MySQL Keeping the Latest Entry for Each Email?

Effective Deletion of Duplicate Records in MySQL with Latest ID Preservation

Problem:

In a MySQL table with both unique ID and email fields, duplicate email addresses arise. The task is to eliminate all but the latest (most recently inserted) instance of each duplicate email while preserving its unique ID.

Solution:

The problem can be solved through a two-step process that identifies and eliminates duplicate emails.

Step 1: Identifying Duplicate Emails

select email 
from test
group by email
having count(*) > 1;

This query returns the list of all emails that appear more than once in the table.

Step 2: Isolating and Deleting Duplicate Records

delete test
from test
inner join (
  select max(id) as lastId, email
  from test
  where email in (
    select email 
    from test
    group by email
    having count(*) > 1
  )
  group by email
) duplic on duplic.email = test.email
where test.id < duplic.lastId;

This query performs a left join between the main table (test) and a subquery (duplic) that contains the latest IDs for each duplicate email. It then deletes all duplicate records with IDs less than the latest ID, effectively preserving only the most recently inserted instance of each email.

Alternative Solution:

A more concise and efficient solution is provided below:

delete from test
where id not in (
  select max(id)
  from test
  group by email
);

This query identifies and deletes all records that are not the latest instances of duplicate emails.

The above is the detailed content of How Can I Delete Duplicate Records in MySQL Keeping the Latest Entry for Each Email?. For more information, please follow other related articles on the PHP Chinese website!