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

Could you use a stored function and call that?

And then inside your stored function you would have both DELETE queries. Then, when the stored function is called it will run as a transaction and you can make it return anything you like.

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.

Answer 3

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 4

That depends on your database engine, but basically what you need sounds like you want one table referencing the other using a FOREIGN KEY with ON DELETE CASCADE option and then removal from the parent table will automatically remove corresponding rows from dependent tables.

Answer 5

Here's a simply solution if you're using MySQL/PgSQL...

DELETE t1, t2 FROM table1 AS t1 
LEFT JOIN table2 AS t2 USING( orderID ) 
WHERE t1.orderID = 500;

Guaranteed to work like a charm!

Make sure to replace table1 and table2 with appropriate table names in your case.

I've used plenty of this query in my custom designed CMS - wherever I've wanted to avoid two distinct atomic queries. This is as good as a cascading delete and the query can be expanded to span over as many tables as you want.

Here's another example involving 3 tables:

DELETE t1, t2, t3 FROM table1 AS t1 
LEFT JOIN table2 AS t2 USING( orderID ) 
LEFT JOIN table3 AS t3 USING( orderID ) 
WHERE t1.orderID = 500;

Cheers, m^e

Answer 6

If you're using InnoDB (or a storage engine that supports them) you can use FOREIGN KEY constraints to delete the correspoding rows. This is one of the easiest/safest approach if you don't mind the slight performance impact of foreign keys. Note however that rows deleted because of constraints do not get triggers to fire.

Alternatively, you can use triggers. They work with any storage engine and they're usually easy enough to write. I like them but they're not very performant if you're deleting a large number of rows at once (several hundreds or thousands.)

CREATE TRIGGER ad_orders AFTER DELETE ON orders
FOR EACH ROW DELETE FROM orders_items WHERE orderID = OLD.orderID;

Finally, as suggested in a previous answer, you could use multi-table DELETE:

DELETE o, oi
FROM orders o
LEFT JOIN orders_items oi USING (orderID)
WHERE o.orderID = 500;