20110604

Removing duplicate rows in mysql

In our recent project called Sfter we have a deamon that do a lot of inserts in a mysql table.
It had a bug that duplicate those rows a LOT! (more than 300 times every record)... today morning i realized we had around 3millon records in that table DAMN!.

So i have to figure out how to remove those duplicates directly using some SQL commands.



From the Mysql manual:


  • IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, the other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.


Pretty simple eh!

No hay comentarios.:

Publicar un comentario