Welcome to WebmasterWorld Guest from 54.146.217.179

Forum Moderators: open

Message Too Old, No Replies

Just a simple question about multiple table delete syntax.

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

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


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.
12:27 pm on July 5, 2012 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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. [...]
3:15 pm on July 5, 2012 (gmt 0)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


thanks for confirming my question.