Here is a fairly quick and painless way to remove duplicate rows from a mySQL database table.

To remove the duplicates in mySQL 3.23 and above we will move the non duplicates into a temporary table, then delete the old table, and rename the temporary table to the old table to finish the circle.

First Create the temporary table and populate it with the values from the old table.

NOTE: As of mySQL 3.23, you can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement :: CREATE TABLE new_tbl SELECT * FROM orig_tbl;

CREATE TABLE newtable
SELECT * FROM oldtable WHERE 1 GROUP BY [column to remove duplicates from];

Now we have a newtable with all of the duplicates removed. So next we drop the old table (the one with all the duplicates still remaining)

DROP TABLE oldtable;

Now we just rename the newtable to the name of the oldtable so we don’t break our whole code base!

RENAME TABLE newtable TO oldtable;

Thats it, duplicates removed. With newer versions of mySQL and with other database engines such as Oracle 9 you can remove duplicates in a single statement and step but this method will still work (using any changes in the SQL commands necessary for your engine).

del.icio.us:Remove duplicate rows / entries from a mySQL database table digg:Remove duplicate rows / entries from a mySQL database table spurl:Remove duplicate rows / entries from a mySQL database table wists:Remove duplicate rows / entries from a mySQL database table simpy:Remove duplicate rows / entries from a mySQL database table newsvine:Remove duplicate rows / entries from a mySQL database table blinklist:Remove duplicate rows / entries from a mySQL database table furl:Remove duplicate rows / entries from a mySQL database table reddit:Remove duplicate rows / entries from a mySQL database table fark:Remove duplicate rows / entries from a mySQL database table blogmarks:Remove duplicate rows / entries from a mySQL database table Y!:Remove duplicate rows / entries from a mySQL database table smarking:Remove duplicate rows / entries from a mySQL database table magnolia:Remove duplicate rows / entries from a mySQL database table segnalo:Remove duplicate rows / entries from a mySQL database table