They say you learn something new every day.

To kick off, and get me ready for next year, something that I figured out today that I’ve been trying to figure out for a long time. How to remove duplicate rows in a SQL database.

I came up with this way of doing it in multiple queries with a “helper” column. For once, stackoverflow.com, and the rest of the Internet couldn’t help me, so I was left to figure it out myself. So, given Table A with columns (1,2,3) add column 4, then run these 3 queries:

INSERT INTO A (1,2,3,4) SELECT DISTINCT 1, 2, 3, “Keep” FROM A

//Copy distinct columns back into the same table, but add a value into the extra blank column

DELETE FROM A WHERE 4 IS NULL

//Delete the rows that don’t have the extra value

UPDATE A SET 4 = NULL

//Blank out the extra values ready for next time 

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

Tag Cloud

%d bloggers like this: