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 =;

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

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

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

No comments: