MySQL Delete Duplicate Rows

This code removes duplicate rows and keeps the first one (the lowest id). We use the same users table from the find duplicates code.

Step 1: check what will be deleted

SELECT u1.*
FROM users u1
JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

This shows rows 3 and 5. These are the extra copies.

Step 2: delete them

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

Now the table has only one row for each email:

Related: MySQL DELETE, MySQL JOINS and Constraints.

• Previous Code
• Next Code

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.