SQL: Delete Duplicate Rows But Keep One, Safely, in Postgres, SQL Server and MySQL

Q: Why should I preview a duplicate-row DELETE with SELECT first?

Because a DELETE built on a wrong PARTITION BY column, a typo, or a case-sensitivity mismatch removes real rows with no warning, and the query often looks correct at a glance. Running the same ranking logic as a plain SELECT first, and checking that rn = 1 lands on the expected row and the count of rn > 1 rows matches the known duplicate count, catches that mistake before it reaches a DELETE statement.

Deleting duplicate rows while keeping exactly one of each starts with numbering the rows within each duplicate group and deleting every number after the first; that is what ROW_NUMBER() OVER (PARTITION BY …) is for, and it works in Postgres and SQL Server with almost identical syntax. MySQL didn’t support window functions before version 8.0, so older MySQL needs a self-join instead, and Postgres has its own shortcut using the hidden ctid column. Every version below is written to be previewed with a SELECT before anything is deleted, run inside a transaction, and followed by a UNIQUE constraint so the duplicates that caused the cleanup can’t come back.

Duplicate rows creep in from a retry that inserted twice, an import run more than once, or a missing constraint that should have stopped them in the first place. The fix looks simple, delete the extras, keep one, until the query is wrong in a way that deletes every copy including the one that was supposed to survive, or deletes rows in a table with no primary key where deleting a specific row isn’t something SQL can express directly. Microsoft’s own documentation gives the ROW_NUMBER() pattern as the standard method, which is a good sign it’s not just community folklore, and it’s the approach that ports cleanly across databases with only the DELETE syntax around it changing. This covers that pattern for Postgres and SQL Server, MySQL’s window-function and pre-8.0 alternatives, and the safety steps worth taking before any of them touch real data.

Preview before deleting anything
Postgres and SQL Server: ROW_NUMBER() in a CTE
Postgres shortcut: ctid
MySQL 8: ROW_NUMBER(), and the self-join for older versions
Wrap it in a transaction
Stop the duplicates from coming back
Related dedup patterns worth knowing
How this fits the rest of the stack
FAQ

Preview before deleting anything

Every pattern below deletes rows based on a ROW_NUMBER() or similar ranking, and the fastest way to turn a good idea into a bad afternoon is running the DELETE before confirming the SELECT version returns exactly the rows expected to go. Run the ranking as a plain query first.

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

Every row with rn = 1 is the one that survives; everything with rn > 1 is a candidate for deletion. Reading through that output, or at minimum checking the count of rows where rn > 1 against the expected number of duplicates, is the check that catches a wrong PARTITION BY column before it deletes the wrong rows instead of after.

This same preview query also doubles as a quick sanity check on the scope of the problem before deciding how urgently to act: a handful of duplicate rows from a one-off retry is a different situation than thousands of rows from a broken import job running on a schedule.

Postgres and SQL Server: ROW_NUMBER() in a CTE

Both databases support deleting from a common table expression built on ROW_NUMBER(), and the pattern is nearly identical between them. PARTITION BY defines what counts as a duplicate, one or more columns that should be unique, and ORDER BY inside the window function decides which copy survives (id ascending keeps the oldest row; descending keeps the newest).

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

SQL Server also supports deleting directly against the CTE without the IN subquery, which some teams prefer for readability.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM ranked WHERE rn > 1;

Postgres requires the IN-subquery form shown first, since Postgres doesn’t allow deleting directly from a CTE the way SQL Server’s syntax does. The ORDER BY inside the window function is doing real work and is easy to get backwards: ascending by an auto-incrementing id keeps the row inserted first, which is usually the intent when duplicates come from a later, redundant insert rather than the original being the mistake. Where PARTITION BY should include more than one column, such as email and tenant_id together defining uniqueness, list them comma-separated inside the same PARTITION BY clause; the rest of the query needs no other change.

Postgres shortcut: ctid

Postgres exposes a hidden system column, ctid, giving each row’s physical location in its table, which offers a shortcut for exactly this kind of dedup: keep the minimum ctid per duplicate group, delete the rest.

DELETE FROM users a
USING users b
WHERE a.ctid < b.ctid
  AND a.email = b.email;

This is fast and needs no window function, but ctid isn’t stable across operations that rewrite the table’s physical storage, such as VACUUM FULL, so it’s a live-query tool, not something to store or rely on between sessions. For a one-off cleanup run interactively, it’s a genuinely simpler query than the ROW_NUMBER() CTE; for anything scripted or repeated, the CTE form is the more durable choice. This form also generalizes past two-way duplicates without any change: a group of five rows sharing the same email still ends up with exactly one survivor, since every row in the group is compared against every other row in the same self-join, not just its immediate neighbor.

MySQL 8: ROW_NUMBER(), and the self-join for older versions

MySQL added window functions, including ROW_NUMBER(), in version 8.0. On 8.0 or later, the pattern matches Postgres closely, with MySQL’s requirement that the target table in a DELETE … WHERE … IN (SELECT …) be wrapped in a derived table subquery to avoid MySQL’s restriction on selecting from the table being deleted.

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

The derived-table wrapping shown here isn’t optional stylistic nesting; MySQL raises error 1093, You can’t specify target table for update in FROM clause, without it, since the DELETE and the SELECT feeding it would otherwise reference the same table directly in a way MySQL specifically disallows.

Before MySQL 8.0, with no window functions available, a self-join comparing each row against every other row with the same key and a higher id does the same job.

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

This keeps the row with the lowest id per email and deletes every row that has a duplicate with a smaller id, which is the self-join equivalent of rn > 1 without a window function to compute it directly.

Wrap it in a transaction

Every query above should run inside an explicit transaction during the first real attempt against production data, not as a matter of paranoia but because a PARTITION BY column that’s slightly wrong, a typo, a column that should have included a second field, a case-sensitivity mismatch, deletes real rows before anyone notices. A transaction turns that mistake into a ROLLBACK instead of a restore from backup. A managed database with automated backups is a second layer under that transaction, in case a mistake reaches COMMIT before anyone notices it.

BEGIN;
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
-- check the row count, check a few surviving rows
COMMIT; -- or ROLLBACK;

Checking the affected row count against the earlier SELECT’s count of rn > 1 rows, inside the same transaction before committing, is the last safety check; if the numbers don’t match, something about the DELETE’s condition differs from the SELECT that was reviewed, and rolling back costs nothing. Autocommit is the default in most client tools and drivers, so the explicit BEGIN matters; without it, each statement commits on its own and the ROLLBACK safety net isn’t there when it’s needed.

Stop the duplicates from coming back

Deleting the duplicates fixes the data as it exists right now; it does nothing about whatever process is still capable of inserting them again tomorrow. A UNIQUE constraint on the column or columns used in PARTITION BY turns a future duplicate insert into a database error the application has to handle, instead of a silent second row that needs cleaning up again next quarter.

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Adding the constraint after the cleanup, not before, matters: attempting it while duplicates still exist fails with a constraint violation naming the first conflicting pair, which is a reasonable way to discover the cleanup missed something, but only useful once the deletion has actually run. A unique index without the CONSTRAINT keyword works the same way for this purpose in most databases, worth knowing as the equivalent term if a team’s migration tooling generates indexes rather than named constraints. Our post on PostgreSQL Not In Queries covers a related pattern worth knowing for filtering rows by absence, which comes up often in the same kind of cleanup work.

ROW_NUMBER() isn’t only for deleting duplicates outright; the same window function numbers rows for pagination, for picking the most recent record per group without a subquery, and for a handful of reporting patterns that have nothing to do with cleanup. Our post on SQL ROW_NUMBER covers the function itself in more depth, including the deduplication use here as one of several practical applications.

For teams doing this kind of cleanup because rows were inserted twice by a retried request rather than a data-entry mistake, the more durable fix sits upstream of the DELETE entirely: an idempotency key on the insert, or the UNIQUE constraint from the previous section doing double duty as both a data-integrity rule and a natural place to use ON CONFLICT DO NOTHING in Postgres or INSERT IGNORE in MySQL so the duplicate insert never lands in the first place. Our post on MySQL DELETE covers the safe-removal side of this in more general terms.

How this fits the rest of the stack

Every variant above does the same job: rank the duplicates, keep the first one, delete the rest, and don’t skip the SELECT-then-transaction sequence just because the query looks obviously right. The UNIQUE constraint at the end is what makes this a one-time cleanup instead of a recurring chore.

If the underlying cause is a database that’s outgrown ad-hoc scripts and manual backups before a risky DELETE, the RunxBuild hosting calculator prices a managed MySQL or Postgres instance with backups and connection limits handled, so a mistake like a wrong PARTITION BY column has a restore point behind it either way.

Useful related references:

FAQ

What is the safest way to delete duplicate rows but keep one in SQL?

Rank the rows within each duplicate group using ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY a tiebreaker like id), preview the results with a plain SELECT to confirm rn = 1 marks the row you want to keep, then run the DELETE inside a transaction so a mistake can be rolled back instead of restored from backup. Add a UNIQUE constraint afterward so duplicates can’t reappear.

Does MySQL support ROW_NUMBER() for deleting duplicates?

Yes, from MySQL 8.0 onward, with the same PARTITION BY pattern used in Postgres and SQL Server, though MySQL requires wrapping the ranking subquery in a derived table to satisfy its rule against selecting from the table being deleted in the same statement. Before 8.0, use a self-join instead: join the table to itself on the duplicate key and delete the row with the higher id.

How do I delete duplicate rows in Postgres using ctid?

Join the table to itself on the duplicate-defining columns and delete the row with the larger ctid, keeping the smaller one: DELETE FROM t a USING t b WHERE a.ctid < b.ctid AND a.key = b.key. It needs no window function and is fast for a one-off cleanup, but ctid isn’t stable across operations like VACUUM FULL, so it isn’t something to rely on between sessions.

Why should I preview a duplicate-row DELETE with SELECT first?

Because a DELETE built on a wrong PARTITION BY column, a typo, or a case-sensitivity mismatch removes real rows with no warning, and the query often looks correct at a glance. Running the same ranking logic as a plain SELECT first, and checking that rn = 1 lands on the expected row and the count of rn > 1 rows matches the known duplicate count, catches that mistake before it reaches a DELETE statement.

How do I stop duplicate rows from coming back after cleanup?

Add a UNIQUE constraint on the column or columns that defined the duplicates. That turns a future duplicate insert into a database error the application must handle instead of a silent second row. Add the constraint after the cleanup runs, not before, since attempting it while duplicates still exist fails immediately with a constraint violation naming the first conflicting pair.