How to remove duplicate data with MySQL

Quite often I have found myself wanting to remove some duplicate data from a MySQL table without having to 1) write a script to de-dupe the table or 2) copy the data into a new table with unique indexes.

Well fortunately there is a quick (and dirty?) solution. Say for example you wanted to get rid of duplicate e-mail addresses in a table, then you could do the following:


By using the IGNORE keyword, it changes the way ALTER TABLE works so that only the first row with a particular e-mail address is kept, further rows containing that e-mail address are simply dropped.

Leave a Reply

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