Mysql: How to clone a table removing duplicate entries

If you have a table in MySql having duplicate entries an you want to remove these duplicate entries you can do that by cloning the table. In MySQL this can be obtained by the following one liner:

CREATE TABLE new table (  `ID` int(11) NOT NULL AUTO_INCREMENT, `col1` int(11) NOT NULL, `col2` varchar(1000) NOT NULL,`col3` varchar(255) NOT NULL,`col4` varchar(30000) NOT NULL, PRIMARY KEY (`ID`)) SELECT DISTINCT * FROM oldtable;

The column definitions are just for demonstration purpose and have to be identical with the original table. By the SELECT DISTINCT query you obtain a copy of the table without duplicate entries.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>