Delete duplicate entries/rows/values in mysql table in one query

Hi all,

During my starting phase of career, i faced several challenging questions (being a fresher at that time). I wanted to share this one as simple as pie but can be difficult at times if you do not know how to do it.

Question can be :

How can you delete or remove duplicate entries from a mysql table, without having to use PHP logic etc ?

The query would be

CREATE TABLE MyNewTable AS SELECT * FROM MyOldTable WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

This would create a new table with name MyNewTable which will have unique values for the column specified in the query. Now we do not require the MyOldTable, hence can be removed, followed by a query that will rename the new table to the old table..

DROP TABLE MyOldTable;
RENAME TABLE MyNewTable TO MyOldTable;

cheers!!
Realin !

Related posts:

PHP Interview for Beginners/Novice/Freshers | Part 1 Insert and update in single mysql query using on duplicate key Difference between InnoDB and MyISAM in MySQL Adding a class to an element using javascript