How to Delete Duplicate Rows in SQL

Duplicate rows mean multiple rows have the same values in one or more columns. To delete duplicates while keeping one copy of each, you can use different approaches depending on your SQL database.

General Approach

1. Identify duplicates using ROW_NUMBER() or GROUP BY.

2. Delete rows where the row number > 1 (keep only one).

Examples by Database

1. Using ROW_NUMBER() (SQL Server, PostgreSQL, Oracle, MySQL 8.0+)

WITH CTE AS (    SELECT *,        ROW_NUMBER() OVER (PARTITION BY column1, column2, column3 ORDER BY (SELECT NULL)) AS rn    FROM your_table)DELETE FROM your_tableWHERE id IN (    SELECT id FROM CTE WHERE rn > 1);

• Replace column1, column2, column3 with columns defining duplicates.

• id is a unique key column to identify rows.

2. In MySQL (Before 8.0 without ROW_NUMBER)

DELETE t1 FROM your_table t1INNER JOIN your_table t2 WHERE     t1.id > t2.id AND     t1.column1 = t2.column1 AND     t1.column2 = t2.column2;

• Keeps the row with the smallest id.

• Replace column1, column2 with the columns defining duplicates.

3. Using GROUP BY and JOIN (Generic method)

DELETE FROM your_tableWHERE id NOT IN (    SELECT MIN(id)    FROM your_table    GROUP BY column1, column2, column3);

• Keeps the row with the minimum id per group.

Important:

• Always backup your data before deleting duplicates!

• Test the SELECT queries first to check which rows will be deleted.

• Adjust the unique key column (id in examples) to your table’s primary key.

If you share your database type and table structure, I can give you an exact query!