DELETE and JOIN in MySQL

Posted on December 11, 2008
by

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.

About the author

Cyril Mazur is a serial web entrepreneur with experience in various fields: online dating, forex & finance, blogging, online advertising... who enjoys building things that people like to use.

7 comments

  1. Andy
    on November 10, 2011
    Awesome! This is the exact problem I was having.
  2. Dave
    on January 28, 2012
    Hi!

    Thank You soo much, this helped me a lot too !!!
  3. Tim
    on August 27, 2012
    DELETE TableA FROM TableA JOIN TableB ON TableA.id = TableB.TableA_id WHERE TabelA.foo = 'bar';

    A few years late, but thought I'd chime in.
  4. Dan
    on June 11, 2013
    Tks a lot! Very, very useful info
  5. Ben
    on April 30, 2014
    Thanks. This is exactly what I was not USING. ;-)
  6. Piotrek
    on March 8, 2016
    Perfect. Works for me, thanks. Wish your woman the best. Women's day today :]

Leave a Reply