Duplicate record removal from MySQL DB table

Man I don’t know how many times I’ve had to do this. I get a list , I want to clean dupes before I import, or after.

and I’d like to do this with a query ( it’s gotta be possible right ? )

so I hunt I test, i break stuff… it sucks, I undo.. I redo.. I blah and I blah.. then it’s done..

waste of time.. so here it is.

First make sure I find what I am looking for :

SELECT *
FROM email_db, email_db AS vtable
WHERE vtable.id > email_db.id
AND vtable.email = email_db.email

very simple and elegant right ?

This is the one I actually ended up using.. to remove the dupes

1. CREATE TABLE temp_table AS SELECT * FROM foo WHERE 1 GROUP BY bar;
2. DROP TABLE foo;
3. RENAME TABLE temp_table TO foo;

Advertisements