MySQL DELETE Query Statement With JOIN

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:

1. DELETE FROM   
2.     `my_table`   
3. WHERE   
4.     `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:

1. DELETE FROM   
2.     `my_table`   
3. INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`  
4. WHERE   
5.     `my_value`='FooBar';  

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

1. #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:

1. DELETE `my_table` FROM   
2.     `my_table`   
3. INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`  
4. WHERE   
5.     `my_value`='FooBar';