Search code examples
mysqlsqldatabasejoininner-join

How to delete rows from two tables using INNER JOIN in mysql?

I want to delete all rows in both tables where the chart_id is 1, but it wont work and I dont have any clue why. 

DELETE `cms_module_charts` 
FROM `cms_module_charts` 
INNER JOIN `cms_module_charts_kategorie` 
ON `cms_module_charts_kategorie`.`chart_id`=`cms_module_charts`.`chart_id`
WHERE `chart_id`= 1

This is the error: Unexpected character. (near "cms_module_charts" at position 7)

Solution

  • From the MySQL Docs it looks like you can do this easily:

     DELETE t1, t2 
   FROM t1 INNER JOIN t2 INNER JOIN t3
  WHERE t1.id=t2.id AND t2.id=t3.id;

    OR

    DELETE 
  FROM t1, t2 
 USING t1 INNER JOIN t2 INNER JOIN t3
 WHERE t1.id=t2.id AND t2.id=t3.id;

    It also looks like the newer, preferable, JOIN standard is acceptable and I have no idea why your query is complaining. Are you sure you haven't got any strange characters in your query?