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!
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.