Info Node: (mysql.info)Deleting from related tables
(mysql.info)Deleting from related tables
Deleting Rows from Related Tables
---------------------------------
As MySQL doesn't support sub-selects or use of more than one table in
the `DELETE' statement, you should use the following approach to delete
rows from 2 related tables:
1. `SELECT' the rows based on some `WHERE' condition in the main
table.
2. `DELETE' the rows in the main table based on the same condition.
3. `DELETE FROM related_table WHERE related_column IN
(selected_rows)'.
If the total number of characters in the query with `related_column' is
more than 1,048,576 (the default value of `max_allowed_packet', you
should split it into smaller parts and execute multiple `DELETE'
statements. You will probably get the fastest `DELETE' by only
deleting 100-1000 `related_column' id's per query if the
`related_column' is an index. If the `related_column' isn't an index,
the speed is independent of the number of arguments in the `IN' clause.