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 < AND =

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 )

About the author

Talifhani Luvhengo

A collection of particles named Tali Luvhengo

Add comment


Page optimized by WP Minify WordPress Plugin