[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

Deleting rows from Linked Tables in one statement?

I am trying to delete rows from a parent/child table relationship and was wondering how to construct such a statement. The parent table (Table 1) has a column called "main_ID". It also has a column called "creep_ID" which is a Foreign Key to a child table (Table 2) where the "creep_ID" is the PK in Table 2.

How can I delete rows from both tables at the same time using the DELETE statement?

Thanks!
0
BlakeMcKenna
Asked:
BlakeMcKenna
  • 4
  • 2
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
One statement, no, unless Cascade Delete is selected.

One transaction, most definately.  Assuming you want to delete rows with a single creep_ID
<air code, so hang with me.  If this doesn't match your situation, you'll have to spell it out in greater detail.>

Declare @creep_ID int = 42

BEGIN TRY

   BEGIN TRAN tr
   DELETE FROM Table2 WHERE creep_ID = @creep_ID
   DELETE FROM Table1 WHERE creep_ID = @creep_ID
   -- If code makes it here, good to go, so commit both
   COMMIT TRAN tr

END TRY

BEGIN CATCH
   -- If code execution here, an error was thrown, so kill the transaction
   ROLLBACK TRAN tr
END CATCH

Open in new window

0
 
Jerry MillerCommented:
I would set up the referential integrity using a CASCADE on the delete. This way when you delete the parent any associated child records are removed without any further effort.

http://technet.microsoft.com/en-us/library/ms186973(v=SQL.105).aspx
0
 
BlakeMcKennaAuthor Commented:
Hi Jim,

Thanks for your response. I was actually just trying to create a single DELETE Statement but based on your code, which is obviously correct, you can't create just a single statement to delete rows from multiple tables. I was thinking that somekind of "JOIN" could be used but I guess not.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
BlakeMcKennaAuthor Commented:
Jerry,

Thank you for your response. I know that's the best way to go as far as good database practice goes. I will probably make that change in our DB.
0
 
BlakeMcKennaAuthor Commented:
Thanks guys for your solutions!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>you can't create just a single statement to delete rows from multiple tables.
Correct.  DELETE and UPDATE affect only one table at a time.  The workaround here is to use transactions, which can ensure that multiple statements either succeed or fail together.

Cascade Update / Delete is certainly an option, but keep in mind that you're playing with fire, as one inadvertent update/delete in one table can cause that same action in other tables, with no way to cancel it.
0
 
Jerry MillerCommented:
As a general rule you should only be deleting updating from within the application, so hopefully there are minimal inadvertent updates. Even when I am doing direct edits in a table, I use queries to make sure that I don't accidently remove the wrong row. I write it as a SELECT first to ensure it is the correct row to update.
0
 
BlakeMcKennaAuthor Commented:
I actually tried running a DELETE statement from SSMS where I created the Foreign Key in the Child Table. When I executed the DELETE on the Parent Table...it didn't delete the rows from the Child Table. So....I may not be setting up the FK correctly. I just posted another question on how to do this.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now