How to delete all rows from another table that are connected with the main one?

Question

I tried something like this, but it does only delete the categories so far.

DELETE s, i 
FROM imgbox_categories s 
INNER JOIN imgbox_images i 
ON s.category_id = i.fk_category_id 
WHERE category_id = @id

Additionaly I get an error

Incorrect syntax near ','.

Answer 1

Since you didn't tag any DBMS involved, you can use EXISTS() which is ANSI SQL:

DELETE from imgbox_categories 
WHERE EXISTS(SELECT 1 FROM imgbox_images  i
             WHERE imgbox_categories.category_id = i.fk_category_id )
 AND imgbox_categories.category_id = @id

Answer 2

Your query seems to be correct, check this answer.

You are trying to delete from two tables at the same time, I think you cannot do this. Here is the comma where you get the error: DELETE s, i

If you delete from imgbox_categories with your query and then from imgbox_images using a simple query, it should work.

Answer 3

If you want delete from 2 tables, you can use 2 SQL queries:

DELETE FROM imgbox_categories WHERE category_id = @id;
DELETE FROM imgbox_images WHERE fk_category_id = @id;