Finding and Removing Duplicates in mysql Database II
A while back i wrote a tutorial on how to remove duplicate rows in a mysql database. That solution worked fine BUT it was ametuarish 🙂
Here’s a better one without a temporary table using the power of self joins.
DELETE u1 FROM users u1, users u2 WHERE u1.id < u2.id AND u1.email = u2.email
This will delete all the rows from users and leave the one with the lowest id (The first one).
To check if there are still duplicates use this query:
SELECT count(*) as Count, email FROM users u group by email having Count > 1
Should return no rows.
FYI. It’s usually a good idea to add a unique key on email 98% of the time.
ALTER TABLE users ADD UNIQUE email ( email )