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

How to Delete Duplicate Records in MySQL While Retaining the Latest

When dealing with tables that contain unique identifiers and duplicate email addresses, it is often necessary to remove these duplicates while preserving the most recent record. MySQL provides several methods to achieve this objective.

One approach involves identifying the email addresses that are repeated and finding the most recent record, represented by the maximum ID, for each of these emails. This can be done using a combination of the GROUP BY, HAVING, and MAX() functions.

Once the most recent records have been identified, the next step is to delete all duplicate records with an ID lower than their respective maximum ID. This can be accomplished with a DELETE statement that uses an INNER JOIN to compare the email addresses and ID values.

Example Database Table

To illustrate this process, consider the following table named "test":

    ID                     EMAIL                
    ---------------------- -------------------- 
    1                      aaa                  
    2                      bbb                  
    3                      ccc                  
    4                      bbb                  
    5                      ddd                  
    6                      eee                  
    7                      aaa                  
    8                      aaa                  
    9                      eee 

Identifying Duplicate Emails

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

This query returns the following result, indicating that 'aaa', 'bbb', and 'eee' are repeated emails:

    EMAIL                
    -------------------- 
    aaa                  
    bbb                  
    eee  

Finding the Most Recent Records

    select max(id) as lastId, email
    from test
    where email in (
        select email 
        from test
        group by email
        having count(*) > 1
    )
    group by email;

This query retrieves the maximum ID and corresponding email address for each duplicate:

    LASTID                 EMAIL                
    ---------------------- -------------------- 
    8                      aaa                  
    4                      bbb                  
    9                      eee                                 

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;

After executing this query, the "test" table will contain only the most recent records for each email address, resulting in the following data:

    +----+-------+
    | id | email |
    +----+-------+
    |  3 | ccc   |
    |  4 | bbb   |
    |  5 | ddd   |
    |  8 | aaa   |
    |  9 | eee   |
    +----+-------+

Optimized Delete Query

An alternative, more optimized delete query is provided below:

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

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