locked
Deleting from 2 tables? RRS feed

  • 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
    Thursday, July 21, 2005 1:03 PM

All replies

  • User-837620913 posted
    If you have a relationship set between the tables, you can set the action in table 1 to "On Delete Cascade".
    Thursday, July 21, 2005 1:08 PM
  • 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
    Thursday, July 21, 2005 1:12 PM
  • 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.
    Friday, July 22, 2005 2:18 PM
  • 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

    Tuesday, July 4, 2006 1:48 PM
  • User-1280683274 posted

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

    Thanks,

    Gary

    Tuesday, July 4, 2006 5:08 PM