MySQL Delete Duplicate Rows but keep one | Delete duplicate rows in mysql except one with Example

MySQL Delete Duplicate Rows But Keep One: If you want to remove duplicate records where id not in select from the table then you can use the MySQL delete function and delete duplicate records in MySQL. In this tutorial, we are going to learn how to delete duplicate rows from a MySQL table and keep only one with the example.

• Delete the duplicate rows but keep latest: using GROUP BY and MAX
• Delete the duplicate rows but keep latest: using JOINS
• Delete the duplicate row but keep oldest: using JOINS
• Delete the duplicate row but keep oldest: using ROW_NUMBER()

Duplicate records can create a serious issue. With duplicate data it is possible for orders can be created many times, It can give inaccurate results.

So here is the number of ways to perform the task of MySQL delete duplicate rows but keep one.

For Example, let’s take a look at the instance of this first by creating a table in a database:

CREATE TABLE team_emails (
    person_id INT AUTO_INCREMENT,
    person_name VARCHAR(255),
    person_email VARCHAR(255),
    PRIMARY KEY (person_id)
);

 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Shikha","shikha@managementbliss.com");
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Numan","numan@gmail.com"); 
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Preeti T","Preeti.Loud@me.com");
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Atharv","Atharv@managementbliss.com"); 
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Nick","Nickk@mine.com"); 
  INSERT INTO team_emails (person_name,person_email) 
 VALUES("Rohit","Rohit.teddy@mine.com"); 
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Aditi Sharma","aditi@managementbliss.com");
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Rohit","Rohit.teddy@mine.com"); 
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Aditi Sharma","aditi@managementbliss.com");
  INSERT INTO team_emails (person_name,person_email) 
 VALUES("Nick","Nickk@mine.com"); 
 INSERT INTO team_emails (person_name,person_email) 
 VALUES("Nick","Nickk@mine.com");

Now from the table, let us see by doing select  * and keeping the rows ordered by person_email .

SELECT * FROM team_emails ORDER BY person_email;

Output:

id     person_name	person_email
7	Aditi Sharma	aditi@managementbliss.com
9	Aditi Sharma	aditi@managementbliss.com
4	Atharv	        Atharv@managementbliss.com
5	Nick	        Nickk@mine.com
10	Nick	        Nickk@mine.com
11	Nick	        Nickk@mine.com
2	Numan	        numan@gmail.com
3	Preeti T	Preeti.Loud@me.com
6	Rohit	        Rohit.teddy@mine.com
8	Rohit	        Rohit.teddy@mine.com
1	Shikha	        shikha@managementbliss.com

So in the above table, the output you can see some duplicates value.

Also Check:

• How to create and execute Stored Procedure in MySQL?
• Java MySQL Database Connectivity with Example

Delete duplicate rows but keep latest: using GROUP BY and MAX

Delete dups mysql: This is the one way to delete duplicate values from the table. After creating the MySQL table we will write the below query and execute it.

DELETE FROM team_emails
 WHERE person_id NOT IN (
   SELECT * FROM (
     SELECT MAX(person_id) FROM team_emails 
       GROUP BY person_email
   )  AS s_alias
 );

After executing the above query we will see output using select *,

Select * From team_emails ;

Output:

id	person_name	person_email
1	Shikha	        shikha@managementbliss.com
2	Numan	        numan@gmail.com
3	Preeti T	Preeti.Loud@me.com
4	Atharv	        Atharv@managementbliss.com
8	Rohit	        Rohit.teddy@mine.com
9	Aditi Sharma	aditi@managementbliss.com
11	Nick	        Nickk@mine.com

So in the above output, you can see that all the duplicates row were deleted.

Delete duplicate rows but keep latest: using JOINS

mysql deleting duplicate rows: In this, we will use joins to delete the old value and keep the latest one. So for doing this we will execute the below query,

DELETE s1 FROM team_emails s1,
    team_emails s2 
WHERE
    s1.person_id < s2.person_id
    AND s1.person_email = s2.person_email;

Now Select * From team_emails ;

Output:

id	person_name	person_email
1	Shikha	        shikha@managementbliss.com
2	Numan	        numan@gmail.com
3	Preeti T	Preeti.Loud@me.com
4	Atharv	        Atharv@managementbliss.com
8	Rohit	        Rohit.teddy@mine.com
9	Aditi Sharma	aditi@managementbliss.com
11	Nick	        Nickk@mine.com

As we can see in the above output, we have successfully deleted the duplicate rows, and the ones with higher person_id are retained. Here we are doing a self join on the same table, which is deleting duplicate records by keeping one copy, the one with a higher value of person_id.

Delete duplicate rows but keep oldest: using JOINS

MYSQI delete duplicate rows: In this, by using joins we will keep the oldest rows value and delete duplicate rows of the one which is new. It is the same as the above one only some changes in the where clause.

DELETE s1 FROM team_emails s1,
    team_emails s2 
WHERE
    s1.person_id > s2.person_id
    AND s1.person_email = s2.person_email;

Now Select * From team_emails ;

Output:

id	person_name	person_email
1	Shikha	        shikha@managementbliss.com
2	Numan	        numan@gmail.com
3	Preeti T	Preeti.Loud@me.com
4	Atharv	        Atharv@managementbliss.com
5	Nick	        Nickk@mine.com
6	Rohit	        Rohit.teddy@mine.com
7	Aditi Sharma	aditi@managementbliss.com

The output shows the oldest rows preserved but the latest duplicate rows deleted. The only change is in the WHERE condition “WHERE s1.person_id > s2.person_id”

Delete the duplicate row but keep oldest: using ROW_NUMBER()

Delete duplicate records in mysql: This is another way to delete the duplicate rows retaining the oldest entries in the table is using ROW_NUMBER () function and PARTITION BY clause.

DELETE FROM team_emails 
WHERE 
    person_id IN (
    SELECT 
        person_id 
    FROM (
        SELECT                         
            person_id,
            ROW_NUMBER() OVER (
                PARTITION BY person_email
                ORDER BY person_email) AS row_num
        FROM 
            team_emails
    ) s_alias
    WHERE row_num > 1
);

Output:

id	person_name	person_email
1	Shikha	        shikha@managementbliss.com
2	Numan	        numan@gmail.com
3	Preeti T	Preeti.Loud@me.com
4	Atharv	        Atharv@managementbliss.com
5	Nick	        Nickk@mine.com
6	Rohit	        Rohit.teddy@mine.com
7	Aditi Sharma	aditi@managementbliss.com

As we can see in the output, the latest duplicate rows got deleted. Here we are using inner queries and ROW_NUMBER() function.

Conclusion on How to delete duplicate records in the table

In the above tutorial, you have seen how to delete the duplicate rows in a MySQL table and keep only one. For example, you can check one of the id value of the row to delete in MySQL table with the help of the following above process. Thank you!