How to Remove Duplicate Data in MySQL Tables Efficiently

A senior architect shares a practical guide for cleaning up duplicate rows in a MySQL database, describing the problem, the initial naive approach, and the correct solutions.

First, identify which name values have duplicates:

SELECT name, COUNT(1)
FROM student
GROUP BY name
HAVING COUNT(1) > 1;

The query returns rows such as cat 2 and dog 2 , indicating that each of these names appears twice.

Attempting to delete all duplicates directly with a sub‑query on the same table causes MySQL error 1093 (you can't specify target table for update in FROM clause).

Solution: use a derived table (or temporary sub‑query) to isolate the duplicate keys before deleting:

DELETE FROM student
WHERE name IN (
  SELECT name FROM (
    SELECT name FROM student
    GROUP BY name
    HAVING COUNT(1) > 1
  ) t
);

This removes every row whose name appears more than once.

If you want to keep a single row per duplicate group (e.g., the row with the smallest id ) and delete the rest, first select the ids to keep and then delete the others:

DELETE FROM student
WHERE id NOT IN (
  SELECT t.id FROM (
    SELECT MIN(id) AS id
    FROM student
    GROUP BY name
  ) t
);

The MIN(id) sub‑query picks the earliest record for each name ; all other rows are removed. This approach runs quickly even on tables with hundreds of thousands of rows.

All steps are demonstrated with example CREATE TABLE and INSERT statements for a sample student table, showing how the techniques work in practice.