Delete rows from two tables in one query

mysql cascade
The Disintegrator picture The Disintegrator · Aug 27, 2009 · Viewed 8.5k times · Source

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

Residuum picture Residuum · Aug 27, 2009

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'.

Related questions

MySQL RESTRICT and NO ACTION

What's the difference in a MySQL FK between RESTRICT and NO ACTION? From the doc they seem exactly the same. Is this the case? If so, why have both?

Read More
MySQL on delete cascade. Test Example

I am wondering about this test question. I prepared the example myself and tested it but I still feel unsure of the answer. With the following: CREATE TABLE foo ( id INT PRIMARY KEY AUTO_INCREMENT, name INT ) CREATE TABLE foo2 ( …

Read More
JPA + Hibernate: How to define a constraint having ON DELETE CASCADE

I am just wondering if there's such a way that I can have build my MySQL table as ALTER TABLE `USERINFO` ADD CONSTRAINT `FK_USER_ID` FOREIGN KEY (`USERID`) REFERENCES `USERACCOUNT` (`USERID`) ON DELETE CASCADE ON UPDATE CASCADE; However, I …

Read More