CODEWITHSUNDEEP
mysqlsqldatabasejoininner-join
Philipp Emmenegger
Philipp Emmenegger

Reputation: 3

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)

Upvotes: 0

Views: 1632

Answers (3)

Arth
Arth

Reputation: 13110

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?

Upvotes: 1

Vincent
Vincent

Reputation: 1044

The standard SQL syntax is DELETE FROM, without nothing butspace between DELETE and FROM

Try this:

DELETE 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

Upvotes: 0

Waluigi
Waluigi

Reputation: 67

i think in you database scheme you should use ON DELETE CASCADE,then when you delete a rows ,it delete its references but when you deleting using join it doesn't make a sense.

Upvotes: 0

Related Questions