How to Efficiently Remove Duplicate Rows in Large MySQL Tables

Background

The online database contains six tables with duplicate data; two of them are large (over 960,000 and 300,000 rows). A previously used Python script that loops through each duplicate row deletes one record per second, resulting in an estimated 8‑hour runtime for ~20,000 duplicates.

Goal

Remove rows that have the same name value, keeping only one record per name.

Identify Duplicates

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

Result: name count(1) cat 2 dog 2

Both cat and dog appear twice.

Attempted Direct Delete (Fails)

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

MySQL error 1093 – you can't specify target table 'student' for update in FROM clause.

The error occurs because MySQL does not allow updating a table while selecting from the same table in a subquery.

Work‑around: Use a Derived Table

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

Delete All Duplicates, Keep One Row

First, view the rows that will be removed:

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

This query keeps the smallest id for each name (the row to retain) and selects all other rows as duplicates.

Execute Deletion

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

The deletion runs very quickly even on tables with more than 900,000 rows.