Delete Duplicate Records in MySQL Keeping One

This guide explains how to safely and efficiently remove duplicate rows from a MySQL database table while preserving a single original record. You will learn how to identify duplicates, back up your data to prevent accidental loss, and execute deletion queries using self-joins and window functions depending on your MySQL version.

Step 1: Back Up Your Data

Before executing any query that modifies or deletes data, always create a backup of your table. This allows you to restore your original state if the deletion query behaves unexpectedly.

CREATE TABLE my_table_backup AS SELECT * FROM my_table;

Step 2: Identify the Duplicates

To ensure you target the correct records, run a selection query to identify which rows have duplicates. Group the data by the columns that define a duplicate (for example, email) and filter for groups with a count greater than one.

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

Method 1: Using an INNER JOIN (Recommended for MySQL 5.x and 8.0)

If your table has a unique primary key auto-increment column (such as id), you can join the table to itself. This query compares records with identical duplicate fields and deletes the row with the larger id, keeping only the record with the lowest (first) id.

DELETE t1 FROM my_table t1
INNER JOIN my_table t2 
ON t1.email = t2.email 
WHERE t1.id > t2.id;

If you want to keep the newest record instead of the oldest, reverse the comparison operator:

DELETE t1 FROM my_table t1
INNER JOIN my_table t2 
ON t1.email = t2.email 
WHERE t1.id < t2.id;

Method 2: Using ROW_NUMBER() (For MySQL 8.0+)

If you are using MySQL 8.0 or newer, you can utilize the ROW_NUMBER() window function. This method partitions your data by the duplicate columns, orders them, assigns a sequential integer to each row, and deletes any row assigned a number greater than 1.

DELETE FROM my_table 
WHERE id IN (
    SELECT id FROM (
        SELECT id, 
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as row_num 
        FROM my_table
    ) temp 
    WHERE row_num > 1
);

Method 3: The Intermediate Table Approach (For Large Tables)

For massive tables, running DELETE operations can lock the database and degrade performance. A safer, faster alternative is to copy the unique records to a temporary table, truncate the original table, and move the unique records back.

-- 1. Create a temporary table with unique records
CREATE TABLE temp_unique_table AS 
SELECT * FROM my_table 
GROUP BY email;

-- 2. Empty the original table
TRUNCATE TABLE my_table;

-- 3. Populate the original table with the unique records
INSERT INTO my_table SELECT * FROM temp_unique_table;

-- 4. Drop the temporary table
DROP TABLE temp_unique_table;

Note: If you use the intermediate table approach, ensure your table is not actively receiving writes during the process to avoid data loss.