Introduction

The best way to deduplicate a table is usually a two-step process: define exactly what counts as a duplicate, then remove or merge rows deterministically while adding constraints that stop the duplicates from returning. The SQL syntax is not the hardest part. The hard part is choosing the right business key and deciding which row should survive.

Define the Duplicate Rule First

Rows are duplicates only relative to some business meaning. Sometimes that means every column matches. More often it means one key or a small key set, such as email, external_id, or customer_id plus order_date.

Start with a grouping query.

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

This tells you which keys are duplicated. It does not tell you which row to keep, so you still need a tie-breaker such as earliest creation time, latest update time, or lowest primary key.

Use ROW_NUMBER to Identify the Extra Rows

Window functions are the clearest general-purpose tool for this job.

1WITH ranked AS (
2    SELECT
3        id,
4        email,
5        created_at,
6        ROW_NUMBER() OVER (
7            PARTITION BY email
8            ORDER BY created_at ASC, id ASC
9        ) AS rn
10    FROM users
11)
12SELECT *
13FROM ranked
14WHERE rn > 1;

This shows the rows that would be removed if you keep the oldest row per email address. Review this output before you run any delete.

Delete Only After You Review the Survivor Logic

Once the ranking rule is correct, use it in a delete statement.

1WITH ranked AS (
2    SELECT
3        id,
4        ROW_NUMBER() OVER (
5            PARTITION BY email
6            ORDER BY created_at ASC, id ASC
7        ) AS rn
8    FROM users
9)
10DELETE FROM users
11WHERE id IN (
12    SELECT id
13    FROM ranked
14    WHERE rn > 1
15);

Run this inside a transaction if your database supports it.

1BEGIN;
2-- inspect duplicate rows
3-- run the delete
4COMMIT;

During development or dry runs, use ROLLBACK instead of COMMIT until you are satisfied with the survivor logic.

Merge Before Delete When Rows Differ

Sometimes duplicate rows are not truly identical. One row may contain the correct phone number while another contains the freshest timestamp. In those cases, deleting extras blindly loses information.

The safer workflow is:

1. choose the survivor row
2. copy any missing or preferred values into it
3. repoint foreign keys if necessary
4. delete the extra rows

That is slower than a one-line delete, but it is the right approach when the duplicates contain partial truth rather than exact copies.

Prevent the Duplicates From Returning

Cleanup is incomplete unless you block recurrence. Add a unique constraint or unique index on the real business key.

ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

If the key should be unique only for certain rows or needs special NULL handling, use the database-specific partial or filtered index features where available.

Also inspect the source of the duplicates. Common causes include race conditions, bad imports, missing validation, or retried jobs with no idempotency key.

Large Tables Need Operational Care

On very large tables, dedupe in chunks or use a staged rewrite if long locks would be dangerous.

1CREATE TABLE users_deduped AS
2SELECT *
3FROM (
4    SELECT *,
5           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC, id ASC) AS rn
6    FROM users
7) t
8WHERE rn = 1;

A staged table lets you validate counts, indexes, and downstream behavior before swapping it into place. That can be safer than in-place deletes on high-volume systems.

Common Pitfalls

• Deduplicating on the wrong business key and deleting valid rows.
• Forgetting a deterministic survivor rule such as timestamp or primary key order.
• Deleting parent rows without considering child foreign keys.
• Cleaning up once and then failing to add a constraint or ingestion fix.
• Running destructive dedupe statements without a transaction, backup, or dry run.

Summary

• Define duplicates using a real business rule before writing deletion SQL.
• Use ROW_NUMBER() to mark which rows should survive and which should go.
• Merge data before deleting when duplicate rows contain different useful values.
• Add a unique constraint or index so the duplicates do not come back.
• For large tables, prefer transactions, dry runs, and staged cleanup over rushed deletes.