MySQL How to delete duplicate records and rows of data


The fastest and easiest way to delete duplicate records is my issuing a very simple command.

alter ignore table [tablename] add unique index `unique_index` ([fieldname])

What this does is create a unique index on the field that you do not want to have any duplicates. The ignore syntax instructs MySQL to not stop and display an error when it hits a duplicate. This is much easier than dumping and reloading a table.

This also will work, but is not as elegant:

delete from [tablename] where fieldname in (select a.[fieldname] from
(select [fieldname] from [tablename] group by [fieldname] having count(*) > 1 ) a )

Leave a Reply

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