How To Delete Duplicate Rows in MySQL

Summary: in this tutorial, you will learn various ways to delete duplicate rows in MySQL.

In the previous tutorial, we have shown you how to find duplicate values in a table. Once the duplicates rows are identified, you may want to delete them to clean up your data.

Prepare sample data

The following script creates the contacts table and inserts sample data into the contacts table for the demonstration.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

DROP TABLE IF EXISTS contacts;   CREATE TABLE contacts (     id INT PRIMARY KEY AUTO_INCREMENT,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     email VARCHAR(255) NOT NULL );   INSERT INTO contacts (first_name,last_name,email) VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),        ('Jean','King','jean.king@me.com'),        ('Peter','Ferguson','peter.ferguson@google.com'),        ('Janine ','Labrune','janine.labrune@aol.com'),        ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),        ('Janine ','Labrune','janine.labrune@aol.com'),        ('Susan','Nelson','susan.nelson@comcast.net'),        ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),        ('Roland','Keitel','roland.keitel@yahoo.com'),        ('Julie','Murphy','julie.murphy@yahoo.com'),        ('Kwai','Lee','kwai.lee@google.com'),        ('Jean','King','jean.king@me.com'),        ('Susan','Nelson','susan.nelson@comcast.net'),        ('Roland','Keitel','roland.keitel@yahoo.com');

You can execute this script to recreate test data after you execute a DELETE statement.

The following query returns the duplicate emails in the contacts table:

1 2 3 4 5 6 7 8

SELECT     email, COUNT(email) FROM     contacts GROUP BY     email HAVING     COUNT(email) > 1;

As you can see, we have four rows with duplicate emails.

A) Delete duplicate rows using DELETE JOIN statement

MySQL provides you with the DELETE JOIN statement that you can use to remove duplicate rows quickly.

The following statement deletes duplicate rows and keeps the highest id:

1 2 3 4 5

DELETE t1 FROM contacts t1         INNER JOIN     contacts t2 WHERE     t1.id < t2.id AND t1.email = t2.email;

The output is:

1	Query OK, 4 rows affected (0.10 sec)

It indicated that four rows have been deleted. You can execute the query that find duplicate emails again to verify the delete:

1 2 3 4 5 6 7 8 9

SELECT     email,     COUNT(email) FROM     contacts GROUP BY     email HAVING     COUNT(email) > 1;

The query returns an empty set, which means that the duplicate rows have been deleted.

Let’s verify data from the contacts table:

1 2 3 4

SELECT     * FROM     contacts;

The rows with id 2, 4, 7, and 9 have been deleted.

In case you want to delete duplicate rows and keep the lowest id, you can use the following statement:

1 2 3 4 5

DELETE t1 FROM contacts t1         INNER JOIN     contacts t2 WHERE     t1.id > t2.id AND t1.email = t2.email;

Note that you can execute the script for creating contacts table again and test this query. The following output shows the data of the contacts table after removing duplicate rows.

B) Delete duplicate rows using an intermediate table

The following shows the steps for removing duplicate rows using an intermediate table:

1. Create a new table with the structure the same as the original table which you want to delete duplicate rows.
2. Insert distinct rows from the original table to the immediate table.
3. Drop the original table and rename the immediate table to the original table.

The following queries illustrate the steps:

Step 1.

1	CREATE TABLE source_copy LIKE source;

Step 2.

1 2 3

INSERT INTO source_copy SELECT * FROM source GROUP BY col; -- column that has duplicate values

Step 3.

1 2	DROP TABLE source; ALTER TABLE source_copy RENAME TO source;

For example, the following statements delete rows with duplicate emails from the contacts table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

-- step 1 CREATE TABLE contacts_temp LIKE contacts;   -- step 2 INSERT INTO contacts_temp SELECT * FROM contacts GROUP BY email;     -- step 3 DROP TABLE contacts;   ALTER TABLE contacts_temp RENAME TO contacts;

C) Delete duplicate rows using ROW_NUMBER() function

Note that the ROW_NUMBER() function has been supported since MySQL version 8.02 so you should check your MySQL version before using the function.

The following statement uses the ROW_NUMBER() function to assign a sequential integer number to each row. If the email is duplicate, the row number will be greater than one.

1 2 3 4 5 6 7 8

SELECT id,     email, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts

The following statement returns the id list of the duplicate rows:

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts ) t WHERE row_num > 1;

And you just delete the duplicate rows from the contacts table using the DELETE statement with a subquery in the WHERE clause:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DELETE FROM contacts WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts ) t     WHERE row_num > 1 );

MySQL issued the following message:

1	4 row(s) affected

In this tutorial, you have learned how to delete duplicate rows in MySQL by using the the DELETE JOIN statement or an intermediate table.

Source: http://www.mysqltutorial.org/mysql-delete-duplicate-rows/