MySQL Remove Duplicate Rows: 6 Ways

To remove duplicate rows in MySQL, you can use the DELETE ... JOIN technique, a temporary table, or the window function ROW_NUMBER() (MySQL 8.0+). This article explains each method step by step, including how to identify duplicates and how to prevent them in the future.

Why Duplicates Happen

Duplicate rows usually appear when a table lacks a unique key or when data is imported from external sources without proper validation. For example, a customer signup form that is submitted twice, or a CSV import that runs twice, can both create identical records. Over time, duplicates inflate table size, slow down queries, and produce misleading report totals.

Identifying Duplicate Rows

Before deleting anything, you need to see which rows are duplicated. Use the GROUP BY clause on the columns that should be unique (e.g., email, order_id, or a combination of fields). For example, to find duplicate emails in a users table:

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

This returns each duplicate value and how many times it appears. If you want to see the actual rows, join back to the original table:

SELECT u.*
FROM users u
JOIN (
    SELECT email, COUNT(*) AS cnt
    FROM users
    GROUP BY email
    HAVING cnt > 1
) d ON u.email = d.email;

Method 1: DELETE JOIN (MySQL 5.7 and earlier)

If you are on MySQL 5.7 or earlier, the classic way is to join the table to itself and delete all but one row per group. The trick is to keep the row with the smallest id (or any unique column).

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id
  AND u1.email = u2.email;

This deletes every row that has a larger id than another row with the same email. After running, only the smallest id for each email remains. This method works on any MySQL version, but it can be slow on large tables because it scans the whole table.

Method 2: Temporary Table (Works on any version)

Another reliable approach is to create a temporary table containing the rows you want to keep, then replace the original table. This is especially useful when you want to keep the first or last occurrence based on a timestamp.

Create a new table with the same structure: CREATE TABLE users_new LIKE users;
Copy distinct rows into it. For example, keep the row with the earliest created_at per email:

INSERT INTO users_new
SELECT u.*
FROM users u
INNER JOIN (
    SELECT email, MIN(id) AS min_id
    FROM users
    GROUP BY email
) keep ON u.id = keep.min_id;

Drop the original table and rename the new one: DROP TABLE users; RENAME TABLE users_new TO users;

This method is safe because you can inspect the new table before dropping the old one. It also works on all MySQL versions.

Method 3: ROW_NUMBER() (MySQL 8.0+)

MySQL 8.0 introduced window functions, making duplicate removal cleaner. Use ROW_NUMBER() to assign a row number per duplicate group, then delete rows where the number is greater than 1.

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
);

The inner subquery is necessary because MySQL does not allow deleting from a table while selecting from it in the same statement. The PARTITION BY clause defines what makes a row a duplicate (here, the email). The ORDER BY decides which row to keep: the one with the smallest id gets rn = 1.

How to Choose the Right Method

Consider the following when picking a method:

MySQL version: If you are on 8.0+, ROW_NUMBER() is often the clearest. On older versions, use DELETE JOIN or the temporary table.
Table size: For very large tables, the temporary table method can be faster if you add an index on the join columns. The DELETE JOIN may lock the table for a long time.
Which row to keep: If you want to keep the most recent row, change ORDER BY id to ORDER BY created_at DESC or use MAX(id) in the temp table approach.
Multiple columns: If duplicates are defined by more than one column, list all of them in the PARTITION BY or in the GROUP BY clause.

Preventing Future Duplicates

After cleaning, add a unique index to prevent duplicates from coming back. For example, if email should be unique:

ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

If duplicates are defined by a combination of columns, create a composite unique index:

ALTER TABLE orders ADD UNIQUE INDEX idx_order (customer_id, order_date);

Be aware that if existing data already contains duplicates, adding a unique index will fail. You must clean the data first.

Common Pitfalls and Misconceptions

One common mistake is using SELECT DISTINCT to “remove” duplicates. That only changes the query result; it does not modify the table. Another pitfall is deleting without a backup. Always export your table or take a snapshot before running destructive queries.

Also, be careful with DELETE JOIN when the table has foreign key constraints. The join may violate constraints if other tables reference the rows you delete. In that case, you may need to handle dependent rows first.

Finally, note that DELETE ... JOIN in MySQL is not the same as the standard SQL DELETE ... USING syntax, but the result is similar. Always test on a copy of your data first.

Frequently Asked Questions

How do I find duplicate rows in MySQL?

Use GROUP BY on the columns that should be unique, then HAVING COUNT(*) > 1. This shows which values appear more than once.

Can I use SELECT DISTINCT to remove duplicates?

No. SELECT DISTINCT only filters the result set. To permanently remove duplicates, you must use DELETE or replace the table.

What is the fastest way to remove duplicates from a large table?

The temporary table method is often fastest because you can create an index on the new table before inserting. The DELETE JOIN method can be slow on millions of rows.

How do I keep the latest row instead of the first?

In ROW_NUMBER(), change the ORDER BY to ORDER BY created_at DESC. In the temporary table method, use MAX(id) or MAX(created_at) in the subquery.

Will adding a UNIQUE index remove existing duplicates?

No. Adding a unique index will fail if duplicates exist. You must delete duplicates first, then add the index.

Is it safe to delete duplicates without a backup?

No. Always back up your table or test on a staging copy. A simple mistake in the WHERE clause can delete more than intended.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →