SQL Query to Delete Duplicate Rows

Remove duplicate records while keeping one copy using CTEs and ROW_NUMBER.

Quick Answer

DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY duplicate_column);

Explanation

Keep the first occurrence (MIN id) of each duplicate group and delete the rest. The CTE method with ROW_NUMBER is more flexible for complex scenarios.

Query Variants

Keep First

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Cte Method

WITH duplicates AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
  FROM users
)
DELETE FROM users WHERE id IN (
  SELECT id FROM duplicates WHERE rn > 1
);

Self Join

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

Pro Tips

• Always backup before bulk deletes
• Test with SELECT first
• Use transactions for safety

Related SQL Queries

Continue learning with more SQL query examples: