Deleting from 2 tables at the same time?

Question

I'm using asp.net and sql server. i have 2 tables: Categories and Products. in the products table i have categoryId as FK. what i want to do is: when i delete category from the category table, i want that all the products from that category will be deleted in the products table. how can this be done ( i prefer with store procedure but its not mandetory)?

Answer 1

@categoryid is coming into the stored proc

delete from products where  categoryid = @categoryid
delete from categories where categoryid = @categoryid

Answer 2

You could define that FK to use DELETES CASCADE. Otherwise, you will need to delete first all the products in the category and then delete the category.

Answer 3

There's a lot of ways to do this. I would set deletes to "Cascade" on your foreign key contraints in SQL. Let SQL manage that for you, it's what it's good at.

Answer 4

If you can adjust the schema, SQL Server supports cascading deletes. With such a FK constraint, you get this effect with a single delete to the category. Not everybody is fond of cascading deletes, mind you!

Answer 5

You can do this by creating a relationship between the tables in the 'Diagrams' section of the database (assuming MS SQL 2005/2008) or the relationship button at the top of the screen (SQL 2000).

Once a one to many relationship has been created with cascade delete the query can simple be:

delete from Categories where CategoryId = XX

This will automatically delete all products associated with the category.

Answer 6

if you want to do it with stored procedure

delete from Categories where categoryId=@categoryId 
delete from Products where categoryId = @categoryId

if this want to happen always .ie if you delete something from Categories table it should delete from Products . my option is DELETE CASCADE.something like this

ALTER TABLE dbo.Products   
WITH CHECK ADD  CONSTRAINT FK_Products_Categories FOREIGN KEY([categoryId])
REFERENCES dbo.Categories([categoryId])
ON DELETE CASCADE

so when you delete from Categories table it automatically deletes from Products table also

e.g : delete from dbo.Categories where categoryId =@categoryId

   no use of writing 

   delete from Products where categoryId = @categoryId

Answer 7

thank u all for the answers, i found the DELETE CASCADE suitble for my needs, now is there anyway to view the changes made in "real time" witout refreshing the page? (im using gridviews for showing both tables in the same page).