Post reply

How to delete data from 2 tables that r linked

  • May 3, 2011 at 7:24 pm

    #235637

    hi

    i want to delete all the records from 2 tables ,that r link with pk->fk

    for ex, emp : empid ,name

    1 , joe

    2 john

    3 sandy

    and emp1 : empid salary

    1 2000

    2 3456

    3 4567

    now i want to delete all the records of john from 2 tables at the same time

    as it is pk->fk i am not able to delele, with single delte statement

    Any suggestion would be appreciate.

    thanks

  • May 3, 2011 at 7:43 pm

    #1319498

    Hi

    You should check ON DELETE CASCADE option.

    Please check BOL:

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/54ca1d10-5106-4340-bde4-480b83e7e813.htm

  • May 3, 2011 at 8:17 pm

    #1319504

    hi

    ON DELETE CASCADE is applied while table creating ,here table is already created

    i cant change them

  • May 3, 2011 at 9:17 pm

    #1319512

    daveriya (5/3/2011)

    hi

    ON DELETE CASCADE is applied while table creating ,here table is already created

    i cant change them

    Can't or not allowed? Cause I'm pretty sure you can change that setting after the table is created.

  • May 3, 2011 at 10:31 pm

    #1319522

    Need to run ALTER TABLE

    Please check BOL for syntax, you can still add ON DELETE CASCADE even if the table is already created.

  • May 3, 2011 at 10:52 pm

    #1319526

    You could also write a trigger yourself,

    something like:

    CREATE TRIGGER trgDel_DeleteChildren

    ON ParentTable

    FOR DELETE

    AS

    delete from ChildTable

    where id in (select id from deleted)

    Id would be refference value between those two tables

    'deleted' is virtual table containing deleted records from parent table

  • May 4, 2011 at 1:32 am

    #1319570

    daveriya (5/3/2011)

    hi

    i want to delete all the records from 2 tables ,that r link with pk->fk

    for ex, emp : empid ,name

    1 , joe

    2 john

    3 sandy

    and emp1 : empid salary

    1 2000

    2 3456

    3 4567

    now i want to delete all the records of john from 2 tables at the same time

    as it is pk->fk i am not able to delele, with single delte statement

    Any suggestion would be appreciate.

    thanks

    If its a adhoc/specifc user's request for deletion of records then its fine to have deletion of child table and then parent ( if required) otherwise OP can have FK "delete on cascade" or "delete dml trigger".

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply