Remove duplicate entries / rows a mySQL database table
This is an extremely quick and painless way to remove duplicate rows (tuples) from a MySQL database table. The best part of it is that it requires no programming or PHP coding whatsoever, it can all be done with three manual SQL queries! Note: this only works on MySQL 3.23 and above. But I have a hard time imagining anyone running an older version than that anyways!
So, there are 3 steps, and therefore 3 SQL statements:
Step 1: Move the non duplicates (unique tuples) into a temporary table
CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];
Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!
DROP TABLE old_table;
Step 3: rename the new_table to the name of the old_table
RENAME TABLE new_table TO old_table;
And of course, don't forget to fix your buggy code to stop inserting duplicates!