Delete A Duplicate Rows In Sql

Introduction

When working with relational databases, duplicate rows can creep into tables and cause data integrity issues, skewed analytics, and bloated storage. Learning how to delete duplicate rows in SQL is a crucial skill for any developer or analyst who wants to keep datasets clean and reliable. This guide walks you through several proven techniques, explains the underlying logic, and offers best practices to ensure you remove duplicates safely without losing essential information.

Understanding Duplicate Rows in SQL

Duplicate rows occur when the same data is inserted more than once, often due to manual data entry, ETL processes, or lack of proper constraints. In SQL, a row is considered a duplicate if it matches another row in all column values. Identifying these rows is the first step before you can delete them. Common signs of duplication include:

• Count mismatches between expected and actual rows.
• Aggregated values (e.g., SUM, COUNT) that appear unusually high.
• Performance degradation because the database must scan extra rows.

Before you delete, it’s wise to create a backup of the table or generate a report of the duplicates you intend to remove.

Methods to Delete Duplicate Rows

Using DELETE with ROW_NUMBER()

The ROW_NUMBER() window function assigns a unique sequential integer to each row within a partition. By filtering on rows where the number is greater than 1, you can delete all but the first occurrence of each duplicate set.

Steps:

1. Identify the columns that define a duplicate set (e.g., id, name, email).
2. Write a CTE that calculates ROW_NUMBER() over those columns.
3. Delete rows where the row number exceeds 1.

WITH DuplicateCTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY col1, col2, col3
            ORDER BY col1
        ) AS rn
    FROM your_table
)
DELETE FROM DuplicateCTE
WHERE rn > 1;

Why it works: The CTE marks the first occurrence of each duplicate group with rn = 1. The subsequent rows receive higher numbers, making them eligible for deletion.

Using a Common Table Expression (CTE) with DELETE

A CTE can also be used without ROW_NUMBER() by joining the table to itself to locate duplicates. This method is useful when you need more control over which duplicate is kept.

Steps:

1. Create a CTE that selects the “keep” rows (e.g., the one with the lowest primary key).
2. Join the original table to the CTE on the duplicate columns.
3. Delete rows that match the duplicates but are not the kept rows.

WITH KeepRows AS (
    SELECT MIN(pk) AS pk
    FROM your_table
    GROUP BY col1, col2, col3
)
DELETE t
FROM your_table t
LEFT JOIN KeepRows k ON t.pk = k.pk
WHERE k.pk IS NULL;

Why it works: The CTE gathers the primary key of the “first” row for each duplicate group. The DELETE statement removes any row whose primary key is not in the keep list Small thing, real impact..

Self‑Join Technique

Self‑joining a table allows you to compare rows directly. In real terms, you can delete the second, third, etc. , occurrences by using a join condition that finds rows where an identical set of column values appears later in the table.

Steps:

1. Join the table to itself on the duplicate columns.
2. Ensure the join includes a condition like t1.pk > t2.pk to keep only one side of the pair.
3. Delete from the “later” side (t1).

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.pk > t2.pk;

Why it works: The join finds matching rows, and the t1.pk > t2.pk condition ensures only the later duplicate is removed, preserving the earliest entry Practical, not theoretical..

Using Temporary Tables

If you prefer a more explicit approach, you can materialize unique rows into a temporary table and then replace the original table. This method is handy for large tables where you want to avoid locking the entire table for an extended period.

Steps:

1. Create a temporary table with the same structure and populate it with distinct rows.
2. Drop or truncate the original table.
3. Insert the data from the temporary table back into the original.

-- Create temp table with unique rows
CREATE TEMPORARY TABLE tmp_unique AS
SELECT DISTINCT *
FROM your_table;

-- Truncate original table
TRUNCATE TABLE your_table;

-- Reload unique data
INSERT INTO your_table
SELECT * FROM tmp_unique;

Why it works: SELECT DISTINCT guarantees only one copy of each row remains. The temporary table acts as a clean intermediate step before restoring the data.

Scientific Explanation

Each method leverages a different SQL feature to identify and eliminate redundancy:

• ROW_NUMBER() creates a deterministic ordering within partitions, making it simple to keep the “first” row and discard the rest.
• CTE with MIN() uses aggregation to pick a representative row per group, which is useful when you need to keep the row with the smallest identifier.
• Self‑join works at the row level, comparing each pair of matching rows and deleting the later one, which can be more intuitive for developers comfortable with relational algebra.
• Temporary tables rely on set operations (DISTINCT) and are often the safest for very large datasets because they minimize the time the original table remains locked.

All approaches assume you have a primary key or a unique identifier that can be used to decide which duplicate to retain. If no such column exists, you must define a deterministic ordering (e.g., by a timestamp or alphabetical order) to avoid arbitrary deletions.

Best Practices and Tips

• Backup first: Always export the table or create a copy before running any delete operation.
• Use transactions: Wrap deletions in a transaction (BEGIN TRANSACTION / COMMIT) so you can roll back if something goes wrong.
• Test on a copy: Run the delete script on a development or staging environment to verify the results.
• Index considerations: Deleting many rows can temporarily disable indexes; ensure you have enough system resources.
• Filter by date or status: If duplicates are recent, add a WHERE clause to limit the impact (e.g., WHERE created_at > '2023-01-01').
• Avoid deleting all duplicates: Some databases may treat DELETE FROM table without a WHERE clause as

an all-or-nothing operation, potentially causing performance issues or even locking the entire database. Always use a targeted WHERE clause to limit the scope of your deletion Still holds up..

Additionally, consider the following advanced tips:

• Batch deletions: For very large tables, delete duplicates in small batches to reduce lock contention and avoid long-running transactions. You can use LIMIT (in MySQL/PostgreSQL) or TOP (in SQL Server) to achieve this.
• Monitor progress: Use database monitoring tools or query execution plans to track the performance of your duplicate removal process.
• Handle foreign key constraints: If your table is referenced by other tables via foreign keys, check that deleting duplicates doesn’t violate referential integrity. You may need to update referencing tables before proceeding.
• Optimize temporary storage: When using temporary tables, ensure they are created on a fast storage medium (e.g., SSD) and cleaned up after use to avoid clutter.

Conclusion

Removing duplicates from a database table is a common yet nuanced task that requires careful planning and execution. While SQL provides multiple methods—such as ROW_NUMBER(), CTEs with aggregation, self-joins, and temporary tables—the best approach depends on factors like table size, available indexes, and the presence of primary keys or unique identifiers.

By understanding the underlying mechanics of each method and following best practices like backing up data, using transactions, and testing in a safe environment, you can confidently eliminate duplicates without compromising data integrity or system performance. Whether you're dealing with a few rows or millions, the key is to choose the right strategy for your specific scenario and execute it with precision.