BIOSTALL

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';

Related Posts

Tags: MySQL

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. You can leave a response, or trackback from your own site.