Deleting from 2 tables?

Question

User-1491916132 posted
Hi, I am pretty new to SQL and know that I am probably going around this the wrong way. I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id. Any help would be greatly appreciated! Many thanks moop

Answer 1

User-837620913 posted
If you have a relationship set between the tables, you can set the action in table 1 to "On Delete Cascade".

Answer 2

User-1491916132 posted
*blush* Thanks for the reply. I just realised that unlike mySql you can issue two deletes in one stored proc like so DELETE FROM tblSupplierType WHERE supplierType_id = @supplierType_id; DELETE FROM tblSuppliers WHERE supplierType_id = @supplierType_id MAny thanks for the reply

Answer 3

User-837620913 posted
I would probably still use the On Delete Cascade option, because if someone comes along and changes your SQL statement later, you'll have a lot of stranded Supplier records.  With On Delete Cascade, it all happens as part of the original delete. 

Either way I would suggest wrapping those two statements in a transaction, and you'll need to reverse the order in which you've shown them.

Answer 4

User-1280683274 posted

I'm trying this using the Club Starter Kit, creating a relationship between the Albums and Images table, where the PK Album ID in the Albums table has a relationship to the Images table via the album field.

When I create the relationship and use the OnDeleteCascase option, I'm receiving this error:

'Albums' table saved successfully
'images' table
- Unable to create relationship 'FK_images_Albums'. 
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_images_Albums". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\GARY\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\MyWebSite\MyWebSite.MDF", table "dbo.Albums", column 'albumid'.

any thoughts on what might be wrong?

I know the PK ablumid is an identity field.  Does that matter?

Thanks,

Gary

Answer 5

User-1280683274 posted

Disregard my previous post.  I was able to get the Cascade to work with Delete.

Thanks,

Gary