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