SQL - Handling Duplicates

SQL - Handling Duplicate Data

Duplicate data in a database can lead to incorrect reports, inconsistent state, and other serious issues. While proper database normalization and constraints (UNIQUE, PRIMARY KEY) are the best ways to prevent duplicates, you often need to find and remove duplicates from existing data.

Method 1: Finding Duplicates with GROUP BY

A simple way to find which values are duplicated is to use GROUP BY and HAVING. This method helps you identify the problem but doesn’t give you the specific rows to delete.

Let’s assume a Users table where the Email column should be unique, but isn’t.

SELECT
    Email,
    COUNT(Email) AS Occurrences
FROM Users
GROUP BY Email
HAVING COUNT(Email) > 1;

This query will return a list of all emails that appear more than once and how many times they appear.

Method 2: Selecting Unique Rows with DISTINCT

The DISTINCT keyword is used to retrieve only unique rows from a query result-set. This is useful for reporting but doesn’t remove the duplicates from the source table.

SELECT DISTINCT Email, Name FROM Users;

A common strategy for cleaning a table is to select the distinct rows into a new temporary table, truncate the original table, and then insert the clean data back.

Method 3: Deleting Duplicates with Window Functions (Advanced)

The most powerful and common method for deleting duplicates while keeping one version of the row uses window functions, specifically ROW_NUMBER().

The strategy is to:

1. Assign a row number to each record, partitioning by the column(s) that define a duplicate (e.g., Email).
2. Order within each partition to decide which row to keep (e.g., the one with the most recent CreatedAt date or the lowest ID).
3. Delete all rows where the row number is greater than 1.

Example

Let’s delete duplicate users based on Email, keeping the one with the lowest ID.

-- This syntax works in PostgreSQL and SQL Server

WITH NumberedUsers AS (
    SELECT
        ID,
        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID ASC) as rn
    FROM Users
)
DELETE FROM Users
WHERE ID IN (
    SELECT ID FROM NumberedUsers WHERE rn > 1
);

In this query:

• The PARTITION BY Email clause groups rows with the same email.
• ORDER BY ID ASC ensures that within each group, the row with the smallest ID gets rn = 1.
• The outer DELETE statement then removes all rows whose row number (rn) is greater than 1, effectively deleting all but the first occurrence of each email.

Prevention is the Best Cure

After cleaning up duplicates, it is crucial to prevent them from happening again. Add a UNIQUE constraint to the relevant column(s).

ALTER TABLE Users
ADD CONSTRAINT UQ_Users_Email UNIQUE (Email);