0

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)

Philipp Emmenegger
  • 3
  • 2

3 Answers3

1

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?

Arth
  • 12,789
  • 5
  • 37
  • 69
0

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
Vincent
  • 1,016
  • 6
  • 11
  • Incorrect, there is an optional multi-table syntax.. look at the docs. – Arth Apr 14 '16 at 14:50
  • @Arth in SQL standard, you have to use this syntax!! maybe mySQL provides an optional syntax, but it is deprecated in this case... – Vincent Apr 14 '16 at 14:52
  • Hmm, I'm pretty sure it's not deprecated.. and the question states that it is for mysql. Simply stating that the syntax is *blah* without any clarification is misleading. – Arth Apr 14 '16 at 15:02
  • OK, but mysql doesn't follow the standards... If you change to PostGreSQL, the closer to standard you make your queries, the less queries you 'll have to rewrite... And for clarification, just see SQL and other SGBD docs, you'll see that you have special syntax only in MySQL... – Vincent Apr 14 '16 at 15:08
  • A valid point, but I'm pretty sure that standard SQL doesn't allow multi-table deletes.. so your syntax may not work anyhow. I'll take the down vote off now as the comments now clarify the answer. – Arth Apr 14 '16 at 15:20
0

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.

Rom
  • 67
  • 1
  • 2
  • 12