Is there a way to delete multiple records from three tables at once in one query?
categories:
id, name
sub_categories:
id, category_id, name
items:
id, subcategory_id, name
I have id of the category that I need to delete. For example, 5
The SQL query must delete the category with that id.
categories.id = 5
Also, it must delete all the subcategories from that category.
sub_categories.category_id = categories.id
And finally, delete all items from those subcategories that where removed in step 2.
items.subcategory_id = sub_categories.id
One way you can delete from multiple tables if you introduce foreign key constraints with ON DELETE CASCADE.
This is the other way around:
DELETE C,SC,I
FROM categories C
INNER JOIN sub_categories SC ON C.id = SC.category_id
INNER JOIN items I ON SC.id = I.subcategory_id
WHERE C.id = 5;
Check this Delete with join(multiple tables)
EDIT:
If sub categories don't have any item under it then you need to replace the last INNER JOIN by LEFT JOIN
DELETE C,SC,I
FROM categories C
INNER JOIN sub_categories SC ON C.id = SC.category_id
LEFT JOIN items I ON SC.id = I.subcategory_id
WHERE C.id = 5;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments