MySQL SQL Delete Notes

To delete duplicates from a MySQL database table I normally copy the duplicate data first to a temporary table and then use the copied id's to delete from the original table.


Delete using the temporary can take two forms. First is the slow way:

delete from target where id in (select id from temp);


This can be agonizingly slow for a large table. A much faster option is:

delete target from target, temp where target.id = temp.id;


BTW: The copying into temporary table part is simple but not that simple. Here's what I do:

insert into temp(id) select b.id from target a, target b where a.common_field1 = b.common_field1 and a.common_field2 = b.common_field2 ... and a.id < b.id;


Note: I am assuming all the tables have an unique row identifier (primary key).

MySQL SQL Delete Notes