GNU Info

(mysql.info)Deleting from related tables

---

Next: No matching rows Prev: Problems with alias Up: Query Issues

---

Enter node , (file) or (file)node

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.