Will code for food; The ramblings of Talifhani Luvhengo;

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 )
Get Adobe Flash player

Page optimized by WP Minify WordPress Plugin