{"id":6314,"date":"2017-07-14T01:12:51","date_gmt":"2017-07-14T08:12:51","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6314"},"modified":"2023-12-29T05:33:52","modified_gmt":"2023-12-29T12:33:52","slug":"mysql-delete-duplicate-rows","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-delete-duplicate-rows\/","title":{"rendered":"How to Delete Duplicate Rows in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various ways to delete duplicate rows in MySQL.<\/p>\n\n\n\n

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

Prepare sample data<\/h2>\n\n\n\n

The following script creates table<\/a> contacts<\/code> and inserts <\/a>sample data into the contacts<\/code> table for the demonstration.<\/p>\n\n\n

DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> contacts;\n\nCREATE<\/span> TABLE<\/span> contacts (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    first_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>, \n    email VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span> contacts (first_name,last_name,email) \nVALUES<\/span> ('Carine '<\/span>,'Schmitt'<\/span>,'carine.schmitt@verizon.net'<\/span>),\n       ('Jean'<\/span>,'King'<\/span>,'jean.king@me.com'<\/span>),\n       ('Peter'<\/span>,'Ferguson'<\/span>,'peter.ferguson@google.com'<\/span>),\n       ('Janine '<\/span>,'Labrune'<\/span>,'janine.labrune@aol.com'<\/span>),\n       ('Jonas '<\/span>,'Bergulfsen'<\/span>,'jonas.bergulfsen@mac.com'<\/span>),\n       ('Janine '<\/span>,'Labrune'<\/span>,'janine.labrune@aol.com'<\/span>),\n       ('Susan'<\/span>,'Nelson'<\/span>,'susan.nelson@comcast.net'<\/span>),\n       ('Zbyszek '<\/span>,'Piestrzeniewicz'<\/span>,'zbyszek.piestrzeniewicz@att.net'<\/span>),\n       ('Roland'<\/span>,'Keitel'<\/span>,'roland.keitel@yahoo.com'<\/span>),\n       ('Julie'<\/span>,'Murphy'<\/span>,'julie.murphy@yahoo.com'<\/span>),\n       ('Kwai'<\/span>,'Lee'<\/span>,'kwai.lee@google.com'<\/span>),\n       ('Jean'<\/span>,'King'<\/span>,'jean.king@me.com'<\/span>),\n       ('Susan'<\/span>,'Nelson'<\/span>,'susan.nelson@comcast.net'<\/span>),\n       ('Roland'<\/span>,'Keitel'<\/span>,'roland.keitel@yahoo.com'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query returns data from the contacts<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> contacts\nORDER<\/span> BY<\/span> email;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The following query returns the duplicate emails in the contacts<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    email, COUNT<\/span>(email)\nFROM<\/span>\n    contacts\nGROUP<\/span> BY<\/span> \n    email\nHAVING<\/span> \n    COUNT<\/span>(email) > 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Delete<\/figure>\n\n\n\n
As you can see, we have four rows with duplicate emails.<\/p>\n\n\n\n
1) Delete duplicate rows using the DELETE JOIN statement<\/h2>\n\n\n\n
MySQL provides you with the DELETE JOIN<\/a><\/code> statement that allows you to remove duplicate rows quickly.<\/p>\n\n\n\n
The following statement deletes duplicate rows and keeps the highest id:<\/p>\n\n\n
DELETE<\/span> t1 FROM<\/span> contacts t1\nINNER<\/span> JOIN<\/span> contacts t2 \nWHERE<\/span> \n    t1.id < t2.id AND<\/span> \n    t1.email = t2.email;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Query<\/span> OK<\/span>, 4 rows<\/span> affected<\/span> (0.10<\/span> sec<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that four rows were deleted. You can execute the query that finds duplicate emails again to verify the delete:<\/p>\n\n\n
SELECT<\/span> \n    email, \n    COUNT<\/span>(email)\nFROM<\/span>\n    contacts\nGROUP<\/span> BY<\/span> \n    email\nHAVING<\/span> \n    COUNT<\/span>(email) > 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Empty set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns an empty set, which means that the duplicate rows have been deleted.<\/p>\n\n\n\n
Let’s verify the data from the contacts<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The rows with id 2, 4, 7, and 9 have been deleted.<\/p>\n\n\n\n
In case you want to delete duplicate rows and keep the lowest id, you can use the following statement:<\/p>\n\n\n
DELETE<\/span> c1 FROM<\/span> contacts c1\nINNER<\/span> JOIN<\/span> contacts c2 \nWHERE<\/span>\n    c1.id > c2.id AND<\/span> \n    c1.email = c2.email;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you can execute the script for creating contacts<\/code> table again and test this query. The following output shows the data of the contacts<\/code> table after removing duplicate rows.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
2) Delete duplicate rows using an intermediate table<\/h2>\n\n\n\n
The following shows the steps for removing duplicate rows using an intermediate table:<\/p>\n\n\n\n
    \n
  1. Create a new table<\/a> with the structure the same as the original table that you want to delete duplicate rows.<\/li>\n\n\n\n
  2. Insert<\/a> distinct rows from the original table to the immediate table.<\/li>\n\n\n\n
  3. Drop the original table<\/a> and rename<\/a> the immediate table to the original table.<\/li>\n<\/ol>\n\n\n\n
    The following queries illustrate the steps:<\/p>\n\n\n\n
    Step 1. Create a new table whose structure is the same as the original table:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> source_copy LIKE<\/span> source<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 2. Insert distinct rows from the original table to the new table:<\/p>\n\n\n
    INSERT<\/span> INTO<\/span> source_copy\nSELECT<\/span> * FROM<\/span> source<\/span>\nGROUP<\/span> BY<\/span> col<\/span>; -- column that has duplicate values<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. Drop the original table and rename the immediate table to the original one<\/p>\n\n\n
    DROP<\/span> TABLE<\/span> source<\/span>;\nALTER<\/span> TABLE<\/span> source_copy RENAME<\/span> TO<\/span> source<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example, the following statements delete rows with duplicate emails from the contacts<\/code> table:<\/p>\n\n\n
    -- step 1<\/span>\nCREATE<\/span> TABLE<\/span> contacts_temp \nLIKE<\/span> contacts;\n\n-- step 2<\/span>\nINSERT<\/span> INTO<\/span> contacts_temp\nSELECT<\/span> * \nFROM<\/span> contacts \nGROUP<\/span> BY<\/span> email;\n\n\n-- step 3<\/span>\nDROP<\/span> TABLE<\/span> contacts;\n\nALTER<\/span> TABLE<\/span> contacts_temp \nRENAME<\/span> TO<\/span> contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    3) Delete duplicate rows using the ROW_NUMBER() function<\/h2>\n\n\n\n
    Note that MySQL supports the ROW_NUMBER()<\/a><\/code>\u00a0function starting from version 8.02 so you should check your MySQL version before using the function.<\/p>\n\n\n\n
    The following statement uses the ROW_NUMBER()<\/a><\/code> function to assign a sequential integer number to each row. If the email is duplicate, the row number will be greater than one.<\/p>\n\n\n
    SELECT<\/span> \n  id<\/span>, \n  email, \n  ROW_NUMBER() OVER<\/span> (\n    PARTITION<\/span> BY<\/span> email \n    ORDER<\/span> BY<\/span> \n      email\n  ) AS<\/span> row_num \nFROM<\/span> \n  contacts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following statement returns id list of the duplicate rows:<\/p>\n\n\n
    SELECT<\/span> \n  id<\/span> \nFROM<\/span> \n  (\n    SELECT<\/span> \n      id<\/span>, \n      ROW_NUMBER() OVER<\/span> (\n        PARTITION<\/span> BY<\/span> email \n        ORDER<\/span> BY<\/span> \n          email\n      ) AS<\/span> row_num \n    FROM<\/span> \n      contacts\n  ) t \nWHERE<\/span> \n  row_num > 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    You need to delete the duplicate rows from the contacts<\/code> table using the DELETE<\/a><\/code> statement with a subquery<\/a> in<\/a> the WHERE<\/a><\/code> clause:<\/p>\n\n\n
    DELETE<\/span> FROM<\/span> \n  contacts \nWHERE<\/span> \n  id<\/span> IN<\/span> (\n    SELECT<\/span> \n      id<\/span> \n    FROM<\/span> \n      (\n        SELECT<\/span> \n          id<\/span>, \n          ROW_NUMBER() OVER<\/span> (\n            PARTITION<\/span> BY<\/span> email \n            ORDER<\/span> BY<\/span> \n              email\n          ) AS<\/span> row_num \n        FROM<\/span> \n          contacts\n      ) t \n    WHERE<\/span> \n      row_num > 1<\/span>\n  );\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    MySQL issued the following message:<\/p>\n\n\n
    4 row(s) affected<\/code><\/span><\/pre>\n\n\n
    In this tutorial, you have learned how to delete duplicate rows in MySQL.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Because we reference the contacts<\/code> table twice, we need to use the table aliases<\/a> t1<\/code> and t2<\/code>.<\/p>\n\n\n\n
Note that you can execute this script to recreate test data after you execute a DELETE<\/a><\/code> statement.<\/p>\n\n\n\n