MySQL DELETE Query Statement With JOIN

December 3rd, 2011 - Posted by Steve Marks to MySQL, Web Development.

Executing a MySQL DELETE query that contains a JOIN… This is something that I always forget how to do so I wanted to write about it as a personal reference, and to help others looking to do the same. First lets take a look at a standard DELETE query containing a single table:

DELETE FROM 
	`my_table` 
WHERE 
	`my_value`='FooBar';

Easy right? Now, the next part is where I was going wrong and I’m guessing is the same in your case. The next statement shows how you might first try to perform the DELETE with a JOIN based on other MySQL syntax:

DELETE FROM 
	`my_table` 
INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`
WHERE 
	`my_value`='FooBar';

By running the above you’ll get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column` WHERE `my_va' ne 1

The Solution

Sticking with the query above we can get it to work with a simple amendment. All we need to do is put the name of the table we’re deleting from between the words ‘DELETE’ and ‘FROM’ like so:

DELETE `my_table` FROM 
	`my_table` 
INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`
WHERE 
	`my_value`='FooBar';
Tags:
This entry was posted on Saturday, December 3rd, 2011 at 3:16 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.
Comments...

Fear not, we won't publish this

Comments (3)
  1. Paul says:

    Thanks, I going through quite some MySQL info websites before I found your golden remark. Karma++!

  2. Gregor says:

    Thanks a lot! Also spent quite a while being frustrated with this error ;D

  3. Johnny says:

    Hi, tnx a lot for this, i spent a hour trying to find a way to delete records from multiply tables (only getting error message). Tnx again :)