Delete Duplicate Rows in MySQL

The duplicate problem

Sometimes a table ends up with duplicate rows — the same customer or email entered twice. You usually want to keep one copy and delete the rest. MySQL offers a couple of clean ways to do this, and the golden rule is to find them first, then delete carefully.

Find duplicates first

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY the columns that define a duplicate, with HAVING COUNT(*) > 1. This shows which values are duplicated before you delete anything.

Delete with a self-join

DELETE t1
FROM users t1
JOIN users t2
  ON t1.email = t2.email
 AND t1.id > t2.id;

This joins the table to itself on the duplicate column, and deletes the row with the higher id whenever a lower-id row has the same email. The result: one copy (the lowest id) of each email survives.

The ROW_NUMBER way

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

In MySQL 8.0+, ROW_NUMBER() numbers copies within each email group. Deleting where rn > 1 removes all but the first of each group — very clear and flexible.

Preventing future duplicates

ALTER TABLE users ADD UNIQUE (email);

After cleaning up, add a UNIQUE constraint so duplicates can never happen again. MySQL will reject any insert that would repeat an email, stopping the problem at the source.

Do it safely

• Run the SELECT first to see exactly which rows will go.
• Back up the table or use a transaction so you can ROLLBACK.
• Double-check the keep rule (lowest id, newest date, etc.).

Deleting duplicates is destructive — a few seconds of checking prevents losing good data.

Common mistakes

• Deleting without keeping one. The join condition must keep a copy.
• Not testing first. Preview with a SELECT before deleting.
• Forgetting to add UNIQUE. Without it, duplicates return.
• Using ROW_NUMBER on old MySQL. Window functions need 8.0+.

Summary

• Find duplicates with GROUP BY ... HAVING COUNT(*) > 1.
• Delete extras with a self-join on id >, keeping the lowest id.
• Or use ROW_NUMBER (MySQL 8.0+) to delete where rn > 1.
• Add a UNIQUE constraint to prevent future duplicates.
• Always test with a SELECT and use a transaction.

Next, practise a related query in finding employees with the same salary, or group data in GROUP BY and HAVING.