Delete rows from two tables in one query

cascade, mysql

Solution

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.

Problem

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?

Original source