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

  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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: