msvrtan's blog

Removing duplicated records in MySQL

Colleague just pointed out cool way of removing duplicate entries in MySQL. We had a 'small' problem in one table where some records got duplicated since we forgot to add UNIQUE index.

So if you want to remove from table "my_favorite_table" all duplicated records and add forgotten unique index use "ALTER IGNORE TABLE" instead of "ALTER TABLE":

ALTER IGNORE TABLE my_favorite_table ADD UNIQUE INDEX name_of_my_unqique_index(column_1, column_2)

Thanks go to Luka Skupnjak for pointing out this cool trick!</p>