1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
Delete duplicate rows in SQL: safe methods

Key takeaways

  • 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 UNIQUE constraint 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 email 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →