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:
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!