none
How to Remove Parent Table & Child table records at a time (when there is a relationship between them)

    Question

  • hello All,

    i do have two table
    1) empl  empid is pk
    2) address  empid is forign key to empl table


    when i try to delete from main table empl it says child rows dependency ,

    how can i remove from the both tables

    i am working as to remove first in address (child table) then main table as below

    1)delete from address where empid in (
    select empid from empl where empid = 100)

    2)delete from empl where empid =100


    is there anyway to write whole as a single rather than two queries, the reason i am asking here is i can write only as a single statement in my expresion builder in some other software.

    i tried by separating go / ; no use
    also i dont want to use cascade delete

    please help me
    thanks in advance
    asitta

    Wednesday, July 21, 2010 9:58 PM

Answers

  • begin transaction

       delete from Address where EmpID = @EmpID -- EmpID to delete

       delete from Empl where EmpID =@EmpID

    commit transaction

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Atif-ullah Sheikh Thursday, July 22, 2010 4:52 AM
    • Marked as answer by KJian_ Wednesday, July 28, 2010 9:38 AM
    Wednesday, July 21, 2010 10:03 PM
    Moderator
  • Hi,

    you can create a trigger (DELETE) for the table Adress and after you use only :

    delete from Address where EmpID = @EmpID

     

    TRIGGER ::


    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [Adress_TR]
      ON [Adress]
      AFTER DELETE
    AS
    BEGIN
    SET NOCOUNT ON;

    declare @EmpID  <type>

    select @EmpID=EmpID from deleted

    Begin Transaction

    delete Empl where EmpID=@EmpID


    Commit Transaction


    END

    Best regards.

     

     

     

    • Marked as answer by KJian_ Wednesday, July 28, 2010 9:38 AM
    Wednesday, July 21, 2010 10:47 PM
  • Apply CASCADE DELETE. It will be easier to implement.

     

    • Marked as answer by KJian_ Wednesday, July 28, 2010 9:38 AM
    Thursday, July 22, 2010 4:51 AM

All replies

  • begin transaction

       delete from Address where EmpID = @EmpID -- EmpID to delete

       delete from Empl where EmpID =@EmpID

    commit transaction

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Atif-ullah Sheikh Thursday, July 22, 2010 4:52 AM
    • Marked as answer by KJian_ Wednesday, July 28, 2010 9:38 AM
    Wednesday, July 21, 2010 10:03 PM
    Moderator
  • Hi,

    you can create a trigger (DELETE) for the table Adress and after you use only :

    delete from Address where EmpID = @EmpID

     

    TRIGGER ::


    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [Adress_TR]
      ON [Adress]
      AFTER DELETE
    AS
    BEGIN
    SET NOCOUNT ON;

    declare @EmpID  <type>

    select @EmpID=EmpID from deleted

    Begin Transaction

    delete Empl where EmpID=@EmpID


    Commit Transaction


    END

    Best regards.

     

     

     

    • Marked as answer by KJian_ Wednesday, July 28, 2010 9:38 AM
    Wednesday, July 21, 2010 10:47 PM
  • Apply CASCADE DELETE. It will be easier to implement.

     

    • Marked as answer by KJian_ Wednesday, July 28, 2010 9:38 AM
    Thursday, July 22, 2010 4:51 AM
  • Hello,

    You can do this as Naom suggested using transactions. If success then deletes from both the tables or else not.

    Create an sproc and follow Naom's suggession, in the other software just call this sproc.

    Hope its clear & helpful....


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, July 22, 2010 4:59 AM