What's wrong with the query?

Question

delete from categories c
left join categories_products cp on cp.category_id = c.id
left join products p on p.id = cp.product_id
left join images i on i.object_id = cp.product_id
where c.id = 3 and i.site_section = 'products'

MySQL returns error. I'm trying to execute this query through the HeidiSQL. Error is unknown.

Another question, which will also help me: how can I make cascade deletion of row if I have no indexes?

score 4 · Accepted Answer · answered Jul 26 '12 at 12:08

4

you should add the alias after the delete keyword

DELETE c FROM categories c
          LEFT JOIN categories_products cp 
                    on cp.category_id = c.id
          LEFT JOIN products p 
                    on p.id = cp.product_id
          LEFT JOIN images i on i.object_id = cp.product_id
WHERE c.id = 3 and i.site_section = 'products'

answered Jul 26 '12 at 12:08

John Woo

258,903
69
498
492

Thanks. But I'm trying to delete row in 'c', 'cp' and 'i' tables, and row deleted only from 'c'. – dearmisterrobot Jul 26 '12 at 12:16
2

this link might help you :) http://stackoverflow.com/questions/734567/delete-rows-from-multiple-tables – John Woo Jul 26 '12 at 12:19
Thanks, the idea is clear. It's just many aliases after DELETE keyword :) – dearmisterrobot Jul 26 '12 at 12:21

juergen d · Answer 2 · 2012-07-26T13:09:03.897

1

delete c from categories c
left join categories_products cp on cp.category_id = c.id
left join products p on p.id = cp.product_id
left join images i on i.object_id = cp.product_id
where c.id = 3 and i.site_section = 'products'

When joining you have to specify from what table you are deleting. That is why it is delete c from ...

edited Jul 26 '12 at 13:09

answered Jul 26 '12 at 12:02

juergen d

201,996
37
293
362

What's wrong with the query?

2 Answers2