Introduction

Deleting duplicates in MySQL starts with one important question: what counts as a duplicate. In most tables, you do not mean rows that are identical in every column. You mean rows that match on some business key, such as email address, while one row should be kept and the others removed.

The safest workflow is to define the duplicate rule, preview the rows that would be removed, and only then run the delete. Duplicate cleanup is one of those tasks where being careful is more important than being clever.

Identify the Duplicate Rule

Suppose this table stores users and you consider rows with the same email to be duplicates:

1CREATE TABLE users (
2    id INT PRIMARY KEY,
3    email VARCHAR(255) NOT NULL,
4    name VARCHAR(255) NOT NULL
5);

Before deleting anything, inspect the duplicates:

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

This tells you which keys appear more than once. It does not delete anything yet, which is exactly what you want at the start.

Delete Duplicates with a Self-Join

A common MySQL pattern is to keep the row with the smallest id and delete the rest:

1DELETE u1
2FROM users AS u1
3JOIN users AS u2
4  ON u1.email = u2.email
5 AND u1.id > u2.id;

How it works:

• 'u1 is the row that may be deleted'
• 'u2 is another row with the same duplicate key'
• 'u1.id > u2.id means u1 is not the smallest id in its duplicate group'

So every duplicate except the kept row is removed.

This is a good general solution because it is easy to reason about and widely supported in MySQL.

Preview Before You Delete

Before running the DELETE, use the same join as a SELECT:

1SELECT u1.*
2FROM users AS u1
3JOIN users AS u2
4  ON u1.email = u2.email
5 AND u1.id > u2.id;

That preview step lets you verify the rows that will be removed. It is especially important when the duplicate key involves multiple columns.

For example, if duplicates are defined by both name and email, change the join condition accordingly.

MySQL 8 Window-Function Alternative

If you are using MySQL 8, you can also rank rows with ROW_NUMBER() and keep the first row in each duplicate group. That can be easier to read for complex duplicate rules, though the delete statement often still ends up using a subquery or join to remove the ranked rows.

The self-join approach remains a strong default because it is straightforward and well understood.

Prevent Duplicates from Coming Back

Cleaning the table once is only half the job. If duplicates are not allowed, add a constraint that prevents them from reappearing.

Example:

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

Without a uniqueness rule, the same data problem can return the next day.

Common Pitfalls

The biggest mistake is deleting duplicates before agreeing on which row should survive. Lowest id is common, but it is only one possible business rule.

Another mistake is running the delete without previewing the affected rows first.

A third issue is forgetting that duplicates may be defined by multiple columns, not just one.

Finally, do not clean the data and stop there. If duplicates are invalid, enforce that rule with a unique index or constraint.

Summary

• Define exactly what counts as a duplicate before deleting anything.
• Preview duplicate groups with GROUP BY ... HAVING COUNT(*) > 1.
• A self-join delete is a practical MySQL pattern for keeping one row and removing the rest.
• Use a preview SELECT before the final DELETE.
• Add a unique constraint afterward if duplicates should never return.