Delete Duplicate Records in SQL: 3 Proven Methods with Examples

Duplicate records corrupt analytical reports, double-count transactional revenue, and trigger primary key constraint violations during ETL migrations. Knowing how to safely delete duplicate records in SQL is both an essential operational task for database administrators and a standard screening question in technical SQL interviews.

In this tutorial, connecting to our primer on what is SQL and subqueries in SQL, we cover the three most reliable methods to delete duplicate records in SQL, examine dialect-specific differences for PostgreSQL, MySQL, and SQL Server, and provide safe transaction templates.

How to Delete Duplicate Records in SQL

Before running any deletion script, you must define:

1. The Duplicate Grain: Which columns define a duplicate? (e.g., email, or (customer_id, transaction_date)).
2. The Retention Strategy: Which duplicate record should be preserved? Typically, you either keep the oldest record (lowest id or earliest created_at) or the newest record (highest id).

Method 1: Delete Duplicate Records in SQL Using ROW_NUMBER() and a CTE

The most modern, ANSI-compliant approach uses the ROW_NUMBER() window function partitioned by the duplicate columns:

PostgreSQL & SQL Server Syntax

In SQL Server, you can delete directly from an updatable CTE:

-- SQL Server Direct CTE Deletion
WITH duplicate_cte AS (
    SELECT 
        id,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY id ASC  -- Keeps the oldest record (rank 1)
        ) AS row_num
    FROM users
)
DELETE FROM duplicate_cte
WHERE row_num > 1;

In PostgreSQL, CTEs are not directly updatable via DELETE, so you filter the table by the IDs identified in the CTE:

-- PostgreSQL CTE Deletion by ID
BEGIN;
 
WITH duplicate_rows AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY id ASC
        ) AS row_num
    FROM users
)
DELETE FROM users
WHERE id IN (
    SELECT id 
    FROM duplicate_rows 
    WHERE row_num > 1
);
 
COMMIT;

Method 2: Delete Duplicate Records in SQL Using Self-Join and Primary Key ID

If your database does not support window functions inside delete statements, or if you prefer a self-contained query, you can join the table to itself on matching duplicate columns:

MySQL Multi-Table DELETE Syntax

MySQL offers a clean multi-table DELETE join syntax:

-- MySQL: Delete duplicates keeping the record with the smaller ID
DELETE u1 
FROM users u1
INNER JOIN users u2 
    ON u1.email = u2.email 
    AND u1.id > u2.id;

Universal SQL Subquery with MIN(id)

This pattern works universally across PostgreSQL, MySQL, Oracle, and SQLite:

-- Universal SQL: Keep MIN(id) per email
DELETE FROM users
WHERE id NOT IN (
    SELECT min_id 
    FROM (
        SELECT MIN(id) AS min_id 
        FROM users 
        GROUP BY email
    ) AS preserved_records
);

(Note: The subquery aliasing FROM (SELECT MIN(id)...) prevents MySQL error 1093 "You can't specify target table for update in FROM clause".)

Method 3: Remove Duplicates from Tables Without a Primary Key

What happens when a poorly designed table has no primary key or unique ID column, and entire rows are complete duplicates?

PostgreSQL: Using Physical ctid

PostgreSQL maintains a hidden physical row location attribute called ctid:

-- PostgreSQL deduplication using hidden physical ctid
DELETE FROM log_events
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM log_events
    GROUP BY user_id, event_name, event_timestamp
);

High-Scale Table Recreation (Fastest for Millions of Rows)

When a table contains millions of duplicate records, running DELETE generates massive transaction write-ahead logs and can lock the table for hours. In high-volume production, creating a clean table and swapping is orders of magnitude faster:

-- Step 1: Create a deduplicated table copy
CREATE TABLE users_deduped AS 
SELECT DISTINCT * FROM users;
 
-- Step 2: Re-add primary keys and indexes
ALTER TABLE users_deduped ADD PRIMARY KEY (id);
CREATE INDEX idx_users_email ON users_deduped(email);
 
-- Step 3: Atomic table swap inside a transaction
BEGIN;
DROP TABLE users;
ALTER TABLE users_deduped RENAME TO users;
COMMIT;

Delete Duplicate Records in SQL: Production Safety, Transactions, and Batching

Executing destructive DELETE statements on multi-million row production tables requires strict defensive protocols. A poorly indexed delete can lock tables, exhaust transaction rollback logs, and degrade active API queries. Learn more across our SQL Tutorials hub.

Wrapping Deduplication Inside Database Transactions

Always wrap deduplication queries inside explicit atomic transactions to verify affected row counts before committing:

BEGIN TRANSACTION;
 
-- Step 1: Count target duplicate records
SELECT COUNT(*) FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) as rn
    FROM users
) t WHERE rn > 1;
 
-- Step 2: Execute deduplication deletion
DELETE FROM users
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) as rn
        FROM users
    ) duplicates
    WHERE rn > 1
);
 
-- Step 3: Audit row count affected. If clean:
COMMIT;
-- If unexpected row count was deleted:
-- ROLLBACK;

Chunked Batch Deletion for High-Volume Production Tables

Deleting 500,000 duplicate records in a single query locks database pages, fills write-ahead logs (WAL), and causes replication lag. In high-traffic transactional environments, execute deduplication in iterative batches:

-- PostgreSQL / MySQL iterative chunked batching pattern
DO $$
DECLARE
    rows_deleted INT := 1;
BEGIN
    WHILE rows_deleted > 0 LOOP
        DELETE FROM audit_logs
        WHERE id IN (
            SELECT id FROM (
                SELECT id, ROW_NUMBER() OVER (PARTITION BY session_id, event_type, event_time ORDER BY id) as rn
                FROM audit_logs
            ) d WHERE d.rn > 1
            LIMIT 5000
        );
        GET DIAGNOSTICS rows_deleted = ROW_COUNT;
        RAISE NOTICE 'Deleted batch of % duplicate records', rows_deleted;
        COMMIT;
        -- Optional brief sleep to allow background I/O to catch up
        PERFORM pg_sleep(0.5);
    END LOOP;
END $$;

Preventing Future Duplicates: Unique Indexes and Constraints

Once clean, lock down the schema to guarantee duplicate rows can never re-enter the system:

-- Case-insensitive unique functional index
CREATE UNIQUE INDEX uq_users_email_lower 
ON users (LOWER(TRIM(email)));

Comparison of Deduplication Techniques

To ensure your database schema inherently prevents duplicate records from ever being inserted again, implement unique constraints using DDL commands in SQL and normalize your entities via normalization in SQL.

Summary Checklist for Deleting Duplicates in SQL

• Clarify which column or combination of columns defines a duplicate record.
• Determine whether to preserve the oldest row (MIN(id)) or the latest row (MAX(id)).
• Run a SELECT query with COUNT(*) > 1 first to inspect duplicate counts.
• Always execute deletion operations inside an explicit BEGIN ... COMMIT block.
• Apply a UNIQUE constraint or index after deduplicating to prevent future duplicates.

Deduplication Architecture: Temporary Staging vs In-Place Deletion

For multi-gigabyte tables where running DELETE triggers severe row-locking and undo-log exhaustion, senior database administrators often prefer the Swap-and-Drop pattern:

-- Step 1: Create a deduplicated target table
CREATE TABLE clean_users AS
SELECT DISTINCT ON (email) *
FROM raw_users
ORDER BY email, created_at DESC;
 
-- Step 2: Recreate primary keys and indexes on the clean table
ALTER TABLE clean_users ADD PRIMARY KEY (id);
CREATE INDEX idx_clean_users_email ON clean_users (email);
 
-- Step 3: Atomic table swap
BEGIN;
DROP TABLE raw_users;
ALTER TABLE clean_users RENAME TO raw_users;
COMMIT;

This approach avoids thousands of individual delete locks and rebuilds pristine, unfragmented B-tree indexes from scratch.

Deduplication Audit Queries Before Final Commit

Always run audit assertions before committing deletions:

-- Assert that no duplicate emails remain in the table
SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

If this verification query returns zero records, your deduplication executed cleanly with zero residual redundant keys.

Related SQL Tutorials

• What Is SQL? The Complete Beginner to Pro Guide
• SQL Joins Explained with Practical Examples
• SQL Window Functions Guide
• SQL Cheat Sheet for Analysts
• Explore All Guides in the SQL Tutorials Hub