? How to Delete Duplicate Rows in SQL

Deleting duplicate rows depends on the SQL database you’re using. The general idea is to keep one copy of each duplicate and remove the others.

Common Approaches:

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

WITH CTE AS (  SELECT *,         ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY (SELECT NULL)) AS rn  FROM table_name)DELETE FROM CTE WHERE rn > 1;

• PARTITION BY columns define duplicates.

• Keeps the first occurrence (rn = 1) and deletes others.

Example:

Suppose duplicates are rows with the same name and email:

WITH CTE AS (  SELECT *,         ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY (SELECT NULL)) AS rn  FROM users)DELETE FROM CTE WHERE rn > 1;

2. Using DELETE with JOIN (MySQL before 8)

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

• Assumes a unique id column.

• Deletes duplicates where t1.id is higher (keeps the row with lowest id).

Example:

DELETE t1 FROM users t1INNER JOIN users t2WHERE   t1.id > t2.id AND  t1.name = t2.name AND  t1.email = t2.email;

3. Using GROUP BY to Find Duplicates

You can find duplicates first:

SELECT name, email, COUNT(*)FROM usersGROUP BY name, emailHAVING COUNT(*) > 1;

Notes:

• Always backup your data before deleting duplicates.

• Test your SELECT queries carefully before DELETE.

• Duplicates are defined by one or more columns that should be unique.

If you tell me your SQL dialect (MySQL, SQL Server, PostgreSQL, etc.), I can give you a precise query!