Deleting rows from Linked Tables in one statement?

Posted on 2014-09-02
Last Modified: 2014-09-02
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?

Question by:BlakeMcKenna
    LVL 65

    Accepted Solution

    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 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
       -- If code execution here, an error was thrown, so kill the transaction

    Open in new window

    LVL 18

    Assisted Solution

    by:Jerry Miller
    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.

    Author Comment

    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.

    Author Comment


    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.

    Author Closing Comment

    Thanks guys for your solutions!
    LVL 65

    Expert Comment

    by:Jim Horn
    >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.
    LVL 18

    Expert Comment

    by:Jerry Miller
    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.

    Author Comment

    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 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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now