SQL Query to Delete Duplicate Rows

Duplicate rows in a database can cause inconsistent results and affect performance. Removing them helps maintain data accuracy and efficiency.

• Caused by import errors or missing constraints.
• Lead to wasted storage and slower queries.
• Can be removed using SQL functions like ROW_NUMBER() or COUNT().

Example: First, we will create a demo SQL table, on which we will use the Delete Duplicate Rows command.

Query:

DELETE FROM Customer
WHERE CustomerID NOT IN (
    SELECT MIN(CustomerID)
    FROM Customer
    GROUP BY FirstName, Country
);

SELECT * FROM Customer;

Output:

Identify Duplicate Rows

We use the GROUP BY clause with the COUNT(*) function to identify duplicate values.

Query:

SELECT first_name, country, COUNT(*) AS duplicate_count
FROM customer
GROUP BY first_name, country
HAVING COUNT(*) > 1;

Output:

• GROUP BY FirstName, Country: Groups customers with the same name and country.
• COUNT(*): Counts the number of rows in each group.
• HAVING COUNT(*) > 1: Shows groups that contain duplicate records.
• Result: Displays the duplicated FirstName and Country combinations.

Methods to Delete Duplicate Rows in SQL

There are several ways to remove duplicate rows from a table.

Using GROUP BY and MIN()

This method keeps the row with the smallest CustomerID for each duplicate group and removes the remaining rows.

Query:

DELETE FROM customer
WHERE customer_id NOT IN (
    SELECT MIN(customer_id)
    FROM customer
    GROUP BY first_name, country
);

SELECT * FROM customer;

Output:

Using ROW_NUMBER()

The ROW_NUMBER() function assigns a unique number to each row within a group. Rows with a number greater than 1 are duplicates and can be deleted.

Query:

WITH duplicate_rows AS (
    SELECT customer_id,
           ROW_NUMBER() OVER (
               PARTITION BY first_name, country
               ORDER BY customer_id
           ) AS row_num
    FROM customer
)
DELETE FROM customer
WHERE customer_id IN (
    SELECT customer_id
    FROM duplicate_rows
    WHERE row_num > 1
);

SELECT * FROM customer;

Output:

Using Common Table Expression (CTE)

A CTE can be used to identify duplicate rows and delete them in a structured way.

Query:

WITH duplicate_rows AS (
    SELECT customer_id,
           ROW_NUMBER() OVER (
               PARTITION BY first_name, country
               ORDER BY customer_id
           ) AS row_num
    FROM customer
)
DELETE FROM customer
WHERE customer_id IN (
    SELECT customer_id
    FROM duplicate_rows
    WHERE row_num > 1
);

SELECT * FROM customer;

Output:

Using a Temporary Table

A temporary table can store unique records before replacing the duplicate data in the original table.

Query:

DROP TEMPORARY TABLE IF EXISTS unique_customers;

CREATE TEMPORARY TABLE unique_customers AS
SELECT *
FROM customer
WHERE customer_id IN (
    SELECT MIN(customer_id)
    FROM customer
    GROUP BY first_name, country
);

DELETE FROM customer;

INSERT INTO customer
SELECT *
FROM unique_customers;

DROP TEMPORARY TABLE unique_customers;

SELECT * FROM customer;

Output:

Using DISTINCT with INSERT INTO

The DISTINCT keyword can be used to select unique customer records before inserting them back into the table.

Query:

CREATE TEMPORARY TABLE unique_customers AS
SELECT DISTINCT first_name, country
FROM customer;

DELETE FROM customer;

INSERT INTO customer (first_name, country)
SELECT first_name, country
FROM unique_customers;

DROP TEMPORARY TABLE unique_customers;

SELECT * FROM customer;

Output:

Why You Should Remove Duplicate Rows

1. Data Integrity: Prevents duplicate records from affecting results.
2. Better Performance: Reduces unnecessary data and improves query efficiency.
3. Efficient Storage: Removes redundant records and saves storage space.