How To Delete The Duplicate Records In Sql

Learn how to delete duplicate records in SQL efficiently using self‑joins, common table expressions (CTEs), and window functions, with step‑by‑step guidance for cleaning your data.

Introduction

Duplicate rows can silently erode the integrity of a database, causing inaccurate reporting, inflated counts, and wasted storage. Whether you are maintaining a customer table, a transaction log, or a product catalog, removing duplicates is a routine but critical task. This article walks you through practical techniques to delete duplicate records in SQL while preserving the data you truly need. Each method is explained with T‑SQL (SQL Server) examples, but the concepts apply to most relational database systems.

Steps to Remove Duplicates

1. Identify the Duplicate Rows

Before you can delete, you must know which rows are duplicates. The typical approach is to group by the columns that define uniqueness and count occurrences Most people skip this — try not to. Less friction, more output..

SELECT col1, col2, col3, COUNT(*) AS dup_count
FROM your_table
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1;

This query returns the columns that have more than one entry, pinpointing the exact duplicates you need to handle.

2. Delete Using a Self‑Join

A self‑join lets you compare rows within the same table and delete the extra copies while keeping one “canonical” row. The pattern is:

DELETE t1
FROM your_table t1
INNER JOIN your_table t2
    ON t1.col1 = t2.col1
   AND t1.col2 = t2.col2
   AND t1.col3 = t2.col3
   AND t1.id < t2.id;   -- keep the row with the higher id

• t1 represents the duplicate row to be removed.
• The join condition matches on the unique key columns.
• The t1.id < t2.id ensures only one row (the one with the larger primary key) survives.

Tip: Replace col1, col2, col3 with the actual columns that should be unique, and adjust the id column to your primary key.

3. Use a Common Table Expression (CTE) with ROW_NUMBER()

When you prefer a more declarative style, a CTE combined with the window function ROW_NUMBER() can mark duplicates for deletion Less friction, more output..

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY col1, col2, col3
               ORDER BY created_at DESC   -- keep the most recent row
           ) AS rn
    FROM your_table
)
DELETE FROM cte
WHERE rn > 1;

• PARTITION BY groups rows that share the same values in the duplicate columns.
• ROW_NUMBER() assigns a sequential integer; the first row (rn = 1) is retained.
• The outer DELETE removes all rows where rn > 1.

This method is especially handy when you need to keep the “latest” or “most important” duplicate based on a timestamp or priority column.

4. Delete via a Temporary Table

For very large datasets, loading duplicates into a temporary table can reduce lock contention and improve performance.

-- 1. Populate a temp table with duplicate IDs
SELECT id
INTO #duplicate_ids
FROM (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY col1, col2, col3
               ORDER BY id
           ) AS rn
    FROM your_table
) AS src
WHERE rn > 1;

-- 2. Delete using the temp table
DELETE t
FROM your_table t
JOIN #duplicate_ids d ON t.id = d.id;

-- 3. Clean up
DROP TABLE #duplicate_ids;

This approach separates the identification and deletion steps, which can be useful in environments where you need to audit or rollback changes Easy to understand, harder to ignore..

Scientific Explanation

How Self‑Joins Work

A self‑join creates a temporary relationship between rows of the same table. Think about it: by matching columns that define uniqueness, the database engine pairs each duplicate with its counterpart. The DELETE statement then removes the “extra” row, leaving the row with the highest primary key (or any other deterministic ordering). This method leverages set‑based operations, which are generally fast and scalable.

Understanding ROW_NUMBER()

ROW_NUMBER() is a window function that assigns a unique sequential integer to rows within each partition. In real terms, g. So naturally, , most recent, highest value). The PARTITION BY clause groups rows that are considered duplicates, while the ORDER BY clause decides which row is kept (e.Because the function operates on a result set before any modification, it provides a clean way to filter duplicates without complex joins The details matter here..

Performance Considerations

• Self‑join can be efficient for small to medium tables but may cause heavy locking on large tables.
• CTE with ROW_NUMBER() is often the most readable and works well with modern query optimizers, though it still scans the whole table.
• Temp table approach spreads the work across two statements, reducing the duration of locks and allowing you to review the IDs before deletion.

Choosing the right method depends on table size, indexing, and the importance of preserving a specific duplicate (e.Here's the thing — g. , the newest record).

Frequently Asked Questions

Q: What if I need to keep the oldest duplicate instead of the newest?
A: Simply change the ORDER BY clause inside ROW_NUMBER() to ORDER BY created_at ASC (or whichever column defines age) Simple as that..

Q: Can I delete duplicates across multiple columns?
A: Yes. Include all columns that together define uniqueness in the PARTITION BY clause (or the join condition for self‑joins).

Q: Will deleting duplicates affect foreign key constraints?
A: If the duplicate rows are referenced by other tables, you must either delete or update those references first, or disable constraints temporarily.

Q: Is there a way to preview which rows will be deleted?
A: Absolutely. Run the same ROW_NUMBER() query without the DELETE to see the duplicate rows and their ranking.

Q: How do I handle duplicates in MySQL or PostgreSQL?
A: The logic is identical; just adjust the syntax (e.g., MySQL uses LIMIT for paginated deletions, PostgreSQL supports

In PostgreSQL the same CTE technique can be expressed with a single statement that references the table’s physical identifier (ctid). For example:

WITH ranked AS (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY created_at DESC) AS rn
    FROM   duplicate_table
)
DELETE FROM duplicate_table
USING ranked
WHERE duplicate_table.ctid = ranked.ctid
  AND ranked.rn > 1;

MySQL does not support the USING clause, but the logic can be achieved with a temporary table or a multi‑table delete wrapped in a transaction:

START TRANSACTION;

CREATE TEMPORARY TABLE tmp_ids AS
SELECT id, ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY created_at DESC) AS rn
FROM   duplicate_table;

DELETE FROM duplicate_table
WHERE (id, name) IN (
        SELECT id, name FROM tmp_ids WHERE rn > 1
      );

COMMIT;

Both engines benefit from an index that covers the columns listed in the PARTITION BY clause. An index on (id, name, created_at DESC) allows the window function to be evaluated using index‑only scans, dramatically reducing I/O.

When the table is very large, deleting all duplicates at once may hold an exclusive lock for an extended period. A common mitigation is to process the data in batches:

WHILE (SELECT COUNT(*) FROM (SELECT 1 FROM duplicate_table LIMIT 1) ) > 0 LOOP
    DELETE FROM duplicate_table
    WHERE ctid IN (
        SELECT ctid FROM (
            SELECT ctid,
                   ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY created_at DESC) AS rn
            FROM   duplicate_table
        ) t
        WHERE rn > 1
        LIMIT 1000
    );
    COMMIT;
END WHILE;

The loop commits after each batch, releasing locks and allowing other sessions to interleave their work.

Additional Tips

• Preview first: Run the SELECT portion of the CTE (or the temporary‑table query) to list the rows that would be removed. Verify the ranking matches the business rule you intend to keep.
• Backup: Even though the operation is set‑based, a point‑in‑time backup or a dump of the affected rows is advisable before mass deletion.
• Foreign‑key impact: If other tables reference the duplicate rows, you must either cascade the delete, update the related rows, or temporarily disable the constraints. Checking ON DELETE CASCADE or ON DELETE RESTRICT definitions beforehand prevents runtime errors.
• Monitoring: Keep an eye on lock wait statistics and transaction log growth, especially on systems with high concurrency. Adjust the batch size if the system shows signs of contention.

Conclusion

Removing duplicate rows is fundamentally a set‑based operation that can be performed safely with a clear, readable query. For very large tables, breaking the delete into smaller batches reduces lock contention and minimizes the risk of long‑running transactions. Proper indexing, previewing the target rows, and respecting foreign‑key constraints are essential preparatory steps. The ROW_NUMBER() window function, wrapped in a CTE, offers the most straightforward syntax across major relational databases and works well with modern optimizers. By following these guidelines, you can eliminate redundancy efficiently while preserving data integrity and maintaining overall database performance.