How to delete data from 2 tables that r linked

  • 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

  • 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

  • hi

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

    i cant change them

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

  • Need to run ALTER TABLE

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

  • 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

  • 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