MySQL delete multiple rows in multiple tables

Question

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

FOREIGN KEY .. ON DELETE CASCADE option will force delete childs when parent is deleted — Serg, Aug 16 '16 at 18:28
A Transaction would seem like a good idea if you dont want to CASCADE DELETE [Start here in the manual for MYSQLI](http://php.net/manual/en/mysqli.begin-transaction.php) or [Start here for PDO](http://php.net/manual/en/pdo.begintransaction.php) — RiggsFolly, Aug 16 '16 at 18:31

score 1 · Answer 1 · answered Aug 16 '16 at 18:28

try ON Delete Cascade

CREATE TABLE categories(
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id )
) ENGINE=InnoDB;


CREATE TABLE sub_categories(
  id int(11) NOT NULL AUTO_INCREMENT,
  category_id int(11) NOT NULL,      
  PRIMARY KEY (id),
  FOREIGN KEY (category_id) 
  REFERENCES categories (category_id) 
  ON DELETE CASCADE
) ENGINE=InnoDB;

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

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;

edited May 23 '17 at 10:33

Community

1
1

answered Aug 17 '16 at 06:39

1000111

13,169
2
28
37

I guess this code will not remove empty sub_categories? – acoder Aug 17 '16 at 17:04
Empty sub categories means? – 1000111 Aug 17 '16 at 17:09
I mean sub_categories that does not contain any items. – acoder Aug 17 '16 at 17:11
In fact this code will remove a particular category, all sub categories under this category and all items under those sub categories – 1000111 Aug 17 '16 at 17:11
Yes you are right. In that case replace the last inner join by left join – 1000111 Aug 17 '16 at 17:12
Updated the answer. Please check now – 1000111 Aug 17 '16 at 17:28

MySQL delete multiple rows in multiple tables

2 Answers2