none
delete both parent and child table records in one query.

    Question

  • Hi experts,

       tblA

    id(pk)

    1

    2

    3

    tblB

    id(fk)

    1

    1

    1

    1

    2

    2

    2

     

    how to delete record from both the table tblA and tblB where tblA id = 1

     

    Saturday, December 25, 2010 8:57 AM

Answers

  • BEGIN TRANSACTION
    DELETE tblB WHERE id = 1
    DELETE tblA WHERE id = 1
    COMMIT TRANSACTION

    You can also set up the foreign-key constraint on tblB as cascading:

    CREATE TABLE tblB (id int NOT NULL,
                       rowno int NOT NULL,
                       -- otherdata
                       CONSTRAINT pk_tblB PRIMARY KEY (id, rowno),
                       CONSTRAINT fk_tblB_tblA FORIEGN KEY (id)
                          REFERENCES tblA (id)
                          *ON DELETE CASCADE*)

    Now it is sufficient to say DELETE tbl WHERE id = 1.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, December 25, 2010 10:23 AM

All replies

  • BEGIN TRANSACTION
    DELETE tblB WHERE id = 1
    DELETE tblA WHERE id = 1
    COMMIT TRANSACTION

    You can also set up the foreign-key constraint on tblB as cascading:

    CREATE TABLE tblB (id int NOT NULL,
                       rowno int NOT NULL,
                       -- otherdata
                       CONSTRAINT pk_tblB PRIMARY KEY (id, rowno),
                       CONSTRAINT fk_tblB_tblA FORIEGN KEY (id)
                          REFERENCES tblA (id)
                          *ON DELETE CASCADE*)

    Now it is sufficient to say DELETE tbl WHERE id = 1.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, December 25, 2010 10:23 AM
  • Hi there,

     You can do this operation in two ways:

    Method-1 first delete the child records which references parent records:

    delete b
    from tblB b
    join tblA a
     on a.id = b.id
    where a.id = 1;

    Method-2 you can put on cascade delete option tblB, to make sure that when you try to delete a parent record in tblA and if the same record exist in tblB it will automatically deletes from tblB as well, to do:

    create table tblB( id int foreign key references tblA (id) on delete cascade);


    Kiran
    Saturday, December 25, 2010 6:31 PM
  • hi ykk28982,

    delete b
    from tblB b
    join tblA a
     on a.id = b.id
    where a.id = 1;

    U r query only deletes the child records not also the parent records.

     

    I want to delete child as well as parent records.

     

     

    Monday, December 27, 2010 5:48 AM
  • Use a query suggested by Erland - in a transaction first delete child records and then parent records.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, December 27, 2010 5:52 AM
  • So i need to write 2 query. can't it be done using only one query without using on delete cascade option.
    Monday, December 27, 2010 6:00 AM
  • Luckyforyou,

    you can not delete 2 tables in one sql statement.

     

     

     

     

     

    • Proposed as answer by satyathetruth Monday, December 27, 2010 6:13 AM
    Monday, December 27, 2010 6:07 AM
  • In T-SQL, DELETE, UPDATE, INSERT operations can only operate upon one table. So, if you don't have CASCADE DELETE relation set between parent and child table, you need to use two delete commands.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, December 27, 2010 3:00 PM
  • > So i need to write 2 query. can't it be done using only one query without using on delete cascade option.

    Nope.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Monday, December 27, 2010 8:36 PM