can i delete rows from 2 tables in one single SQL statement?

Question

Hi there.

I have 2 tables one called "REVIEW" and the other "REVIEW_DESCRIPTION"

if i do a join to get all the data from both:

SELECT *
FROM reviews 
INNER JOIN reviews_description
ON reviews.reviews_id=reviews_description.reviews_id
WHERE reviews.customers_id = 54183

based on this i would like to delete rows in both tables that match this criteria in one shot, any tips?

thanks!

Answer 1

would cascade delete help?

Answer 2

Unless you have on cascade delete or a delete trigger setup you need to use 2 statements

Answer 3

Yes, MySQL supports deletions from more than one table in a single statement:

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching...

First multi-table syntax example:

DELETE reviews, reviews_description 
  FROM reviews r
  JOIN reviews_description rd
 WHERE reviews.reviews_id = reviews_description.reviews_id
   AND reviews.customers_id = 54183