Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step
To delete duplicate rows in SQL, number the rows in each duplicate group with ROW_NUMBER() and delete every row numbered above 1, or keep the MIN(id) per group. This guide shows how to find, preview and remove duplicates safely, then stop them coming back.
- To delete duplicate rows in SQL, decide first which copy to keep (oldest, newest or most complete), then delete the rest.
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)is the most flexible method;MIN(id)is the simplest.- Our data had 3 exact duplicates by email, but 4 once we ignored case and spaces. Normalise before you count.
- Copying into a new table lost the primary key in our test. Add a
UNIQUEconstraint afterwards so duplicates cannot return.
Duplicate rows creep in from double-clicked forms, retried imports and missing constraints. They inflate counts, send customers two emails and break joins. Deleting them is easy to get wrong: one careless query removes every copy, including the one you meant to keep. Below are five methods we ran in SQLite 3.50.4, each with its result. The SQL is standard and works with small changes in PostgreSQL, MySQL 8+, SQL Server and Oracle, which we note but did not run.
Quick answer
DELETE FROM customers
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created, id) AS rn
FROM customers
) WHERE rn > 1
);
Each group of rows with the same email is numbered from 1, oldest first. Every row with a number above 1 is a duplicate and gets deleted. Change the ORDER BY to choose which copy survives.
Sample data
| id | name | created | |
|---|---|---|---|
| 1 | asha@example.com | Asha | 2026-01-05 |
| 2 | ravi@example.com | Ravi | 2026-01-06 |
| 3 | asha@example.com | Asha K | 2026-02-10 |
| 4 | ASHA@example.com␠ | Asha | 2026-03-01 |
| 5 | li@example.com | Li | 2026-01-07 |
| 6 | ravi@example.com | Ravi | 2026-01-06 |
| 7 | li@example.com | Li | 2026-04-02 |
Row 4 has capitals and a trailing space (shown as ␠), a realistic “near duplicate”.
Step 1: find the duplicates
SELECT email, COUNT(*) FROM customers
GROUP BY email HAVING COUNT(*) > 1;
[('asha@example.com', 2), ('li@example.com', 2), ('ravi@example.com', 2)]
Now normalise the value before grouping:
SELECT lower(trim(email)) AS e, COUNT(*) FROM customers
GROUP BY e HAVING COUNT(*) > 1;
[('asha@example.com', 3), ('li@example.com', 2), ('ravi@example.com', 2)]
Asha has three records, not two. Decide what “duplicate” means for your data before deleting anything. GROUP BY and HAVING are explained in our GROUP BY and HAVING guide.
Step 2: preview what will be deleted
Run the inner query as a SELECT first:
SELECT id, email, created FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created, id) rn
FROM customers
) WHERE rn > 1;
[(3, 'asha@example.com', '2026-02-10'), (7, 'li@example.com', '2026-04-02'), (6, 'ravi@example.com', '2026-01-06')]
Tip: back up the table or run inside a transaction (BEGIN … ROLLBACK) while testing. Check the row count, and only then COMMIT. Our SQL transactions guide shows how.
Method 1: keep the lowest ID with MIN()
DELETE FROM customers
WHERE id NOT IN (SELECT MIN(id) FROM customers GROUP BY email);
Deleted 3 rows and kept ids 1, 2, 4 and 5. It is short and works almost everywhere. Two cautions: it keeps the lowest ID, not necessarily the best record, and row 4 survived because exact matching treats ASHA@example.com␠ as a different email. In MySQL, wrap the subquery in a derived table, because MySQL refuses to read from the table it is deleting from.
Method 2: ROW_NUMBER (choose which copy to keep)
DELETE FROM customers WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (
PARTITION BY lower(trim(email))
ORDER BY created DESC, id DESC) AS rn
FROM customers
) WHERE rn > 1
);
This version groups on the cleaned email and keeps the newest record. It deleted 4 rows and kept ids 4, 6 and 7. Notice the catch: the survivor for Asha is row 4, which still holds the messy value ASHA@example.com␠. After removing duplicates, run an UPDATE to clean the kept value, or keep a different row. ROW_NUMBER is explained in our RANK vs DENSE_RANK vs ROW_NUMBER guide.
Method 3: EXISTS (delete if an older twin exists)
DELETE FROM customers
WHERE EXISTS (
SELECT 1 FROM customers c2
WHERE c2.email = customers.email AND c2.id < customers.id
);
Deleted 3 rows, the same as Method 1. It reads naturally (“delete me if an older copy exists”) and is a self join in disguise; see our SQL self join guide. Without an index on the compared columns, it can be very slow on large tables, because the subquery runs for every row.
Method 4: exact duplicates with no ID column
When rows are identical in every column, there is nothing to tell them apart. SQLite has a hidden rowid, PostgreSQL has ctid and Oracle has ROWID:
CREATE TABLE t(a, b);
INSERT INTO t VALUES (1,'x'), (1,'x'), (2,'y'), (1,'x');
DELETE FROM t WHERE rowid NOT IN (SELECT MIN(rowid) FROM t GROUP BY a, b);
Deleted 2 rows and left (1, 'x') and (2, 'y'). In SQL Server, use a CTE with ROW_NUMBER and delete from the CTE directly.
Method 5: copy distinct rows into a new table
CREATE TABLE c2 AS
SELECT * FROM customers
WHERE id IN (SELECT MIN(id) FROM customers GROUP BY email);
DROP TABLE customers;
ALTER TABLE c2 RENAME TO customers;
This left 4 rows, but when we inspected the new table its definition was just id INT, email TEXT, ...: the primary key was gone. CREATE TABLE AS copies data, not constraints, indexes or triggers. The method can be fastest for huge tables where most rows are duplicates, but you must recreate everything else by hand. Our guide to DELETE vs TRUNCATE vs DROP explains what dropping removes.
Speed test on 200,000 rows
We created 200,000 rows with 100,000 distinct emails, so half were duplicates, and timed each delete on a fresh copy:
| Method | Rows deleted | Time |
|---|---|---|
NOT IN (SELECT MIN(id) ...) |
100,000 | 0.17 s |
ROW_NUMBER() |
100,000 | 0.23 s |
EXISTS with index on (email, id) |
100,000 | 0.17 s |
All three finished in under a quarter of a second in SQLite; only the EXISTS version had an extra index, on (email, id). We did not time EXISTS without an index, because it has to search the table again for every row. On a production server, also consider deleting in batches of a few thousand rows to keep locks short.
Stop duplicates coming back
CREATE UNIQUE INDEX ux_email ON customers(email);
INSERT INTO customers(email, name, created) VALUES ('li@example.com', 'Li', '2026-05-01');
sqlite3.IntegrityError: UNIQUE constraint failed: customers.email
A unique constraint is the only real cure; cleanup queries just treat the symptom. Store emails in lowercase and trimmed, or index the normalised expression where your database supports it. On the application side, use an upsert to update instead of inserting a second copy; see our guides to SQL constraints and SQL upsert.
Which method should you use?
| Situation | Method |
|---|---|
| Keep the oldest, simple data | MIN(id) |
| Keep newest or most complete record | ROW_NUMBER with custom ORDER BY |
| Readable, well-indexed table | EXISTS |
| Identical rows, no key | rowid, ctid or ROWID |
| Huge table, most rows duplicated | New table, then recreate constraints |
Syntax notes for each database
We ran everything above in SQLite. The same ideas work elsewhere with small syntax changes; these patterns are standard for each engine but were not run for this article:
| Database | Typical approach |
|---|---|
| PostgreSQL | DELETE ... WHERE id IN (SELECT id FROM (... ROW_NUMBER() ...) t WHERE rn > 1); use ctid when there is no key |
| MySQL 8+ | Wrap the subquery in a derived table, or use DELETE c1 FROM customers c1 JOIN customers c2 ON c1.email = c2.email AND c1.id > c2.id |
| SQL Server | WITH d AS (SELECT *, ROW_NUMBER() OVER (...) rn FROM customers) DELETE FROM d WHERE rn > 1 |
| Oracle | DELETE FROM customers WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM customers GROUP BY email) |
Whatever the engine, keep the same safety routine: preview with SELECT, delete inside a transaction, compare the deleted count with the preview, and only then commit.
Final thoughts
To delete duplicate rows in SQL safely, follow the same four steps every time: define what a duplicate is, preview the rows with a SELECT, delete inside a transaction and check the count, then add a unique constraint. ROW_NUMBER gives you the most control over which copy survives; MIN(id) is the quickest for simple cases. Clean, constraint-protected data saves far more time than any clever delete query.
Frequently asked questions
How do I delete duplicate rows but keep one in SQL?
Number each duplicate group with ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY id) and delete rows where the number is greater than 1, or delete rows whose id is not the MIN(id) of their group.
How do I find duplicate rows in SQL?
Group by the columns that define a duplicate and filter with HAVING COUNT(*) > 1. Normalise values such as email with lower and trim first if case or spaces vary.
How do I remove duplicates when the table has no primary key?
Use the database row identifier, such as rowid in SQLite, ctid in PostgreSQL or ROWID in Oracle, or in SQL Server delete from a CTE that uses ROW_NUMBER.
Is DISTINCT enough to remove duplicates?
DISTINCT only hides duplicates in query results. The rows stay in the table, so you still need a DELETE and a unique constraint to fix the data.
How do I prevent duplicate rows in SQL?
Add a UNIQUE constraint or unique index on the columns that must not repeat, normalise values before saving, and use an upsert instead of a plain insert.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.