Duplicate record removal from MySQL DB table
September 27, 2009
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
- CREATE TABLE temp_table AS SELECT * FROM foo WHERE 1 GROUP BY bar;
- DROP TABLE foo;
- RENAME TABLE temp_table TO foo;
Advertisements