Welcome to WebmasterWorld Guest from 54.146.246.4

Forum Moderators: open

Message Too Old, No Replies

Just a simple question about multiple table delete syntax.

     

nelsonm

5:02 pm on May 2, 2012 (gmt 0)

5+ Year Member



Hi all,

I want to use a single delete statement to delete records from multiple tables.

The MySQL manual appears to say that the following two statements are functionally identical.

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

Am i correct?

The only difference appears to be semantic and as such, i prefer the latter "DELETE FROM a1, a2 USING" syntax. It seems more natural.

coopster

12:27 pm on Jul 5, 2012 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You are referencing the ALIAS section of the multiple-table delete syntax on that page of the MySQL manual. But yes, you are correct. Look up a little higher on that page and you will find the statement that clarifies:

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. [...]

nelsonm

3:15 pm on Jul 5, 2012 (gmt 0)

5+ Year Member



thanks for confirming my question.