MySQL + SQL · Lesson 1
Delete Duplicate Rows
The Problem
Remove duplicate rows from a table while keeping one copy of each.
Using a Self Join
-- keep the row with the smallest id, delete the rest
DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.id > e2.id
AND e1.email = e2.email;How it Works
We compare rows with the same email; for each duplicate pair we delete the one with the larger id, keeping the first.
Summary
- Self-join the table on the duplicate column, delete rows with the larger id.
- This keeps one copy of each unique value.