DELETE and JOIN in MySQL

I noticed something weird today, while attempting to do a DELETE query with a JOIN statement. The syntax was simple though:

DELETE FROM
  tableA
JOIN
  tableB ON tableA.tableB_id = tableB.id
WHERE
  tableA.foo = 'bar'

And it doesn’t work! It’s a syntax error according to MySQL. Therefore this join statement works good in a SELECT query. So SELECT and DELETE don’t have the same behavior regarding to joins. A simple solution is to use the USING statement.

My query is now:

DELETE FROM
  tableA
USING
  tableA
JOIN
  tableB ON tableA.tableB_id = tableB.id
WHERE
  tableA.foo = 'bar'

Weird, by adding our USING, it works. Don’t really understand why, but it works!

In the general case, according to mysql.com, the syntax is the following:

DELETE FROM
  table1, table2
USING
  table1
JOIN
  table2 ON [**onClause**]
JOIN
  table3 ON [**onClause**]
WHERE
  [**whereClause**]

With this syntax, the matched rows will be deleted only in the tables listed before the USING (so table1 and table2, but not table3)

To sumarize, if a DELETE query using JOIN doesn’t work, just think about adding a USING statement.