how to delete from another 2 table using one key?

Question

I have a table named
Category_tbl

Id
Categoryname
info

subcategory_tbl

id
categoryid
subcatname
info

product_tbl

id
subcat
info

i in here subcategory_tbl has the category id and product_tbl has subcategory id as subcat.now if i want to delete a category then i all the corresponding data of same key in subcategory_Tbl and product_tbl needs to be deleted.how can i do it?i tried joining but its not working

First delete the data from the tables which have foreign key and then the table with primary key..Using multiple delete statements.... — G one, Feb 04 '14 at 06:23
You mean to say that subcat of product_tbi is not the foreign key?? and so is the category_id of subcate_tbi?? — G one, Feb 04 '14 at 06:28
Try cascading for the below given link http://www.mysqltutorial.org/mysql-on-delete-cascade/ — शु-Bham, Feb 04 '14 at 06:29

score 0 · Answer 1 · edited May 23 '17 at 10:25

Without cascading deletes on the foreign keys, what you can do this as a sequence of deletes from subcategory through product and finally category. Assuming you wish to delete category 123:

delete 
from product_tbl
where subcatid in
(
  select id
  from subcategory_tbl
  where categoryid = 123
);

delete 
from subcategory_tbl
where categoryid = 123;

delete 
from Category_tbl
where id = 123;

If however you do have ON DELETE CASCADE defined on both foreign keys, all you would need to do is delete the category and the product and subcategories would be deleted as well. Dangerous, but effective.

score 0 · Answer 2 · answered Feb 04 '14 at 06:48

Unfortunately their isn't a way to delete in the way you are asking..As the tables are not related to each other in any manner..Just by having a column with equal values to other table, one cant associate the delete command to delete the rows of both the tables...

There has to be some criteria by which we can join the tables and then delete the rows..which we do not have here...

score 0 · Accepted Answer · answered Feb 04 '14 at 06:57

0

First delete the data from the tables which have foreign key and then the table with primary key..Using multiple delete statements...

answered Feb 04 '14 at 06:57

Borshon saydur rahman

130
2
10

how to delete from another 2 table using one key?

3 Answers3