Lesson 12.5 · Reading time: ~7 min

Duplicate rows appear in almost every real database: an import runs twice, a form is submitted twice, a unique constraint was never added. "Find duplicates" and "delete duplicates but keep one" are classic SQL interview tasks. In this lesson you'll find duplicate values with GROUP BY ... HAVING, list every copy with a window function, delete the extra copies safely in MySQL, and prevent new duplicates with a constraint.

Find and Delete Duplicate Rows in SQL

First decide what a duplicate is. Rows are rarely identical in every column: they usually have different ids and creation times. A duplicate means the same business key: the same email, the same name and birth date, the same order number. Every query below groups by that key.

The examples use MySQL 8.0 syntax and create their own table, so run them in the playground on a database without sample data.

Sample Data

CREATE TABLE contact (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    full_name VARCHAR(50) NOT NULL
);

INSERT INTO contact VALUES
    (1, 'anna@mail.com', 'Anna Lee'),
    (2, 'bob@mail.com', 'Bob Stone'),
    (3, 'anna@mail.com', 'Anna Lee'),
    (4, 'carl@mail.com', 'Carl Diaz'),
    (5, 'bob@mail.com', 'Bob Stone'),
    (6, 'Anna@Mail.com', 'Anna Lee'),
    (7, 'dina@mail.com', 'Dina Park');

Step 1: Find Duplicate Values

Group by the key and keep the groups with more than one row:

SELECT email, COUNT(*) AS copies
FROM contact
GROUP BY email
HAVING COUNT(*) > 1;

Result: anna@mail.com with 3 copies and bob@mail.com with 2.

Notice that Anna@Mail.com was counted together with anna@mail.com. MySQL's default collation compares strings case-insensitively, so they are the same value. PostgreSQL compares case-sensitively and would find only 2 copies of Anna; there, group by LOWER(email) to get the same answer. For a key of several columns, list them all: GROUP BY first_name, last_name.

Step 2: See Every Duplicate Row

GROUP BY shows one row per value. To see the duplicate rows themselves, with their ids, count the copies with a window function and keep the rows where the count is above one:

WITH counted AS (
    SELECT id, email, full_name,
           COUNT(*) OVER (PARTITION BY email) AS copies
    FROM contact
)
SELECT id, email, full_name, copies
FROM counted
WHERE copies > 1
ORDER BY email, id;

Result: 5 rows: ids 1, 3, 6 for Anna and 2, 5 for Bob.

Before deleting anything, look at these rows: do the copies really mean the same thing, and which one should stay?

Step 3: Mark the Copies to Delete

Number the rows inside each group with ROW_NUMBER(). The row numbered 1 is the one to keep; here the oldest, with the smallest id:

SELECT id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contact
ORDER BY email, id;

Result: ids 1, 2, 4, 7 get rn = 1; ids 3, 5, 6 get 2 or 3 and are the extra copies.

ORDER BY inside the window decides which row survives: ORDER BY id DESC keeps the newest, ORDER BY updated_at DESC the most recently updated.

Step 4: Delete the Extra Copies

In MySQL, a subquery in DELETE can't read the table being deleted from (error 1093). Wrap it in a derived table, which MySQL materializes first:

DELETE FROM contact
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM contact
    ) AS numbered
    WHERE rn > 1
);

Result: 3 rows deleted; ids 1, 2, 4 and 7 remain, one per email.

An older MySQL-only form uses a self-join: delete every row for which a row with the same email and a smaller id exists:

DELETE c
FROM contact c
JOIN contact k ON k.email = c.email AND k.id < c.id;

Result: the same 3 rows deleted.

In PostgreSQL, the usual form is DELETE FROM contact c USING contact k WHERE k.email = c.email AND k.id < c.id;; in SQL Server you can delete directly from a CTE with ROW_NUMBER().

Run the SELECT from step 3 before the DELETE, compare the number of affected rows, and in production run the deletion inside a transaction.

Step 5: Prevent New Duplicates

Cleaning up helps only until the next import. A unique constraint makes the database reject duplicates:

ALTER TABLE contact ADD CONSTRAINT uq_contact_email UNIQUE (email);

Add it after the cleanup: while duplicates exist, creating the constraint fails. Imports can then use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE (Lesson 8.1) to skip or update existing rows.

Practice

• Duplicate Actor Names
• Duplicate Actor Surnames
• Duplicate Emails

Key Takeaways:

• A duplicate is a repeated business key, not necessarily an identical row; group by that key.
• GROUP BY key HAVING COUNT(*) > 1 finds duplicate values; COUNT(*) OVER (PARTITION BY key) shows every duplicate row.
• ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) marks one row to keep (rn = 1) and the copies to delete.
• In MySQL, delete through a derived table or a self-join to avoid error 1093.
• Case and collation decide what counts as equal; finish with a UNIQUE constraint.

Frequently Asked Questions

How do I find duplicate rows in SQL?

Group by the columns that should be unique and filter the groups: SELECT email, COUNT(*) FROM contact GROUP BY email HAVING COUNT(*) > 1;. To see the full rows, use COUNT(*) OVER (PARTITION BY email) and keep the rows where it is greater than 1.

How do I delete duplicates but keep one row?

Number the rows of each group with ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) and delete the rows with a number greater than 1. In MySQL, put the numbering into a derived table inside the DELETE, or use a self-join: DELETE c FROM t c JOIN t k ON k.key = c.key AND k.id < c.id.

Why does MySQL show error 1093 when I delete duplicates?

MySQL doesn't allow a subquery in DELETE or UPDATE to read the same table directly. Wrapping the subquery in a derived table (SELECT id FROM (...) AS d) makes MySQL materialize it first, and the error disappears.

How do I find duplicates in several columns?

List all of them in GROUP BY and in PARTITION BY: GROUP BY first_name, last_name HAVING COUNT(*) > 1. Rows are duplicates only when all the listed columns match.