Search code examples
mysqlsqlmyisam

MySQL, how to merge table duplicates entries

Possible Duplicate:
How can I remove duplicate rows?
Remove duplicates using only a MySQL query?

I have a large table with ~14M entries. The table type is MyISAM ans not InnoDB.

Unfortunately, I have some duplicate entries in this table that I found with the following request : 

SELECT device_serial, temp, tstamp, COUNT(*) c FROM up_logs GROUP BY device_serial, temp, tstamp HAVING c > 1

To avoid these duplicates in the future, I want to convert my current index to a unique constraint using SQL request :

ALTER TABLE  up_logs DROP INDEX UK_UP_LOGS_TSTAMP_DEVICE_SERIAL,
ALTER TABLE up_logs ADD INDEX UK_UP_LOGS_TSTAMP_DEVICE_SERIAL (  `tstamp` ,  `device_serial` )

But before that, I need to clean up my duplicates!

My question is : How can I keep only one entry of my duplicated entries? Keep in mind that my table contain 14M entries, so I would like avoid loops if it is possible.

Any comments are welcome!

Solution

  • Creating a new unique key on the over columns you need to have as uniques will automatically clean the table of any duplicates.

    ALTER IGNORE TABLE `table_name`
    ADD UNIQUE KEY `key_name`(`column_1`,`column_2`);

    The IGNORE part does not allow the script to terminate after the first error occurs. And the default behavior is to delete the duplicates.