SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Delete from two tables

    So I've been trying to figure this one out this morning.

    I have two tables, work_history and work_history_detail. Not every work_history has a work_history_detail.

    I am trying to delete the records in work_history that fit a certain criteria and the corresponding records in work_history_detail (there is a request_id field in both tables).

    Any suggestions? Should I delete from work_history and then go delete the records out of work_history_detail that don't have a record in work_history?

    This is SQL Server.

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    You can use a FOREIGN KEY and have it CASCADE on delete, so it'll delete all the related ones when the related row in work_history is deleted. I think this is a standard syntax, supported on the InnoDB engine.

  3. #3
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Upon further research it seems that this is not possible in SQL Server (deleting from two tables) but is possible in other database servers.

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i thought you could use transactions to in this case delete from your work history and then delete from your history detail if these are successfuly do transaction commit, if no transaction rollback.

    this should insure data integrity.

  5. #5
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ^^^This is the correct answer. Note SQL server has implicit transactions and can take chained statements, so you could do some thing like this [code is C# and rough around the edges]:

    Code:
    using (SqlConnection conn=GetConnection())
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
             cmd.CommandText="DELETE FROM work_history WHERE requestId=@requestId; DELETE FROM work_history_details WHERE requestId=@requestId";
             cmd.Parameters.AddWithValue("@requestId", requestId);
             conn.Open();
             cmd.ExecuteNonQuery();
             conn.Close();       
        }
    }

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    actually, the correct answer is ON DELETE CASCADE

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    If those two things are actually so intrinsically related that there should be a cascading deletion going on? What if there were some odd cases where business rules decreed that one must keep the child objects indefinitely? Not that there is anything wrong with ON DELETE CASCADE, but it is not the magic bullet. In most cases I would still manually fire it from code--much easier to test and maintain than DDL IMHO.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    What if there were some odd cases where business rules decreed that one must keep the child objects indefinitely?
    then you would not define the foreign key with ON DELETE CASCADE

    duh!!!

    however, the original question was "I am trying to delete the records in work_history that fit a certain criteria and the corresponding records in work_history_detail (there is a request_id field in both tables)."

    ask yourself under what circumstances does it make sense to keep a work_history_detail row for a work_history request_id that doesn't exist??????

    i didn't think so

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, the correct answer is ON DELETE CASCADE

    Well, I didn't design the tables (though I wish they would put me in charge of redesigning them) so I don't have control over that.

    I knew that I could do it that way so I guess I should have put that in my original post.

    wwb_99, I was doing this directly in SQL Server, no .NET involved.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •