Removing duplicate rows in MySQL

Duplicate rows are one of the most common data problems we get asked to fix. The textbook answers both have drawbacks:

Copy the unique rows into a new table, drop the old one, rename. This resets AUTO_INCREMENT, breaks foreign keys and triggers, and needs privileges you may not have on a production database.
Add a UNIQUE index on the "key" columns. Often impossible: a VARCHAR(1024) column in utf8mb4 can take up to 4,096 bytes, more than InnoDB's 3,072-byte index key limit. And the index only stops new duplicates; it does not remove the existing ones.

A better approach is to delete the redundant rows in place, keeping the row with the lowest primary key in each group of duplicates.

Option 1: self-join (any MySQL version)

The inner query finds every group of duplicates and the smallest id in it. Joining it back to the same table selects every other row of the group, which we delete.

In this example, a row in offers is a duplicate if title, provider_id and city_id all match:

DELETE bad_rows
FROM offers AS bad_rows
INNER JOIN (
    SELECT title, provider_id, city_id, MIN(id) AS min_id
    FROM offers
    GROUP BY title, provider_id, city_id
    HAVING COUNT(*) > 1
) AS good_rows
    ON  good_rows.title       = bad_rows.title
    AND good_rows.provider_id = bad_rows.provider_id
    AND good_rows.city_id     = bad_rows.city_id
    AND good_rows.min_id     <> bad_rows.id;

Join on every column you group by. Joining on title alone would also delete rows that share a title but belong to a different provider or city.

Option 2: window function (MySQL 8.0+, MariaDB 10.2+)

ROW_NUMBER() numbers the rows inside each group, so everything after the first row is a duplicate:

DELETE o
FROM offers AS o
INNER JOIN (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY title, provider_id, city_id
               ORDER BY id
           ) AS rn
    FROM offers
) AS ranked ON ranked.id = o.id
WHERE ranked.rn > 1;

Changing ORDER BY id lets you choose which copy survives. For example, ORDER BY updated_at DESC keeps the most recently updated one.

Before you run it

Run the inner SELECT first and check the row count.
Take a backup, or run the delete inside a transaction you can roll back.
Afterwards, prevent new duplicates: add a UNIQUE index if the columns allow it, or enforce uniqueness in the application.

The self-join technique is described in more detail in Baron Schwartz's classic article, How to delete duplicate rows with SQL.