remove duplicates from mysql tables

December 19th, 2006 •
tags: mysql

#temporary table to hold the duplicate free data
create temporary table good_temp
(
  id int(11) unsigned not null auto_increment primary key,
  name varchar(255)
) TYPE=HEAP;

# assigning fresh auto_incremented ids without holes
insert into good_temp(name) select distinct(name) from bad order by id;

# delete old records containing duplicates
delete from good;

# insert duplicate free records from temporary table
insert into good(name) select * from good_temp;