CODEWITHSUNDEEP
asp.netsql-serverstored-procedures
tone
tone

Reputation: 1555

Deleting from 2 tables at the same time?

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)?

Upvotes: 2

Views: 532

Answers (6)

anishMarokey
anishMarokey

Reputation: 11397

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

Upvotes: 1

w4ymo
w4ymo

Reputation: 312

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.

Upvotes: 0

Pontus Gagge
Pontus Gagge

Reputation: 17258

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!

Upvotes: 2

Ben Lesh
Ben Lesh

Reputation: 108491

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.

Upvotes: 1

BobbyShaftoe
BobbyShaftoe

Reputation: 28499

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.

Upvotes: 2

John Boker
John Boker

Reputation: 83709

@categoryid is coming into the stored proc

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

Upvotes: 1

Related Questions