Delete rows from two tables in one query

Question

I have two tables: orders and orders_items. Both sharing the field orderID.

I want to delete all rows from both tables where orderID=500, but I need to do this in only one query. Is this possible?

Answer 1

Surely you can do that:

DELETE FROM `table1`, `table2` WHERE `orderId` = 500

see http://dev.mysql.com/doc/refman/5.0/en/delete.html

[EDIT:]

This is the whole trick:

DELETE FROM `orders`, `orders_items` 
  USING `orders` 
  INNER JOIN `orders_items` ON `orders`.`orderId` = `orders_items`.`orderId` 
  WHERE `orders`.`orderId`= 500

If orderId is a varchar, then change the statement to = '500'.

Answer 2

You can define the table with ON DELETE CASCADE. If you do that, you only have to delete on the order table. The entries in other tables using order_id as foreign key with that option enabled will be deleted automagically.

This example is taken from the MySQL manual:

CREATE TABLE parent(
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child(
    id INT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;

Note that the engine is InnoDB.