locked
Query need for 2 table update RRS feed

  • Question

  • I have 2 tables
    Table1                Table2
    ----------            -----------
    unitnum            unitnum
    Code                Code
    CodeDesc        CodeDesc
    CodeType        CodeType


    I need to do this.

    If a row is present in Table2 , but not in table1, then I need to insert the record in table1.
    If a row is present in Table1 and table2, but the CodeType is different in Table2, then I need to update the record in Table1.
    If a row is present in Table1, but not in Table2, then i need to delete the record in Table1.

    can anybody suggest me a query for this.

    Thanks.
    NSG12
    Thursday, January 22, 2009 5:56 PM

Answers


  • INSERT Table1
    SELECT * FROM Table2 t2
    WHERE NOT EXISTS
       (SELECT 1 FROM Table1 t1 WHERE
        t1.unitnum=t2.unitnum
        AND t1.CodeDesc=t2.CodeDesc
        and    t1.Code=t2.Code
        and t1.CodeType=t2.CodeType)   


    UPDATE Table1
    SET CodeType = t2.CodeType
    FROM Table2 t2
    INNER JOIN Table1 t1 ON
        t1.unitnum=t2.unitnum
        AND t1.CodeDesc=t2.CodeDesc
        and and    t1.Code=t2.Code

    DELETE FROM table1 WHERE unitnum  not IN
    (SELECT t2.unitnum FROM table2 t2 WHERE
        table1.unitnum=t2.unitnum
        AND table1.CodeDesc=t2.CodeDesc
        and    table1.Code=t2.Code
        and table1.CodeType=t2.CodeType)



    • Proposed as answer by Mark Steinberg Thursday, January 22, 2009 10:47 PM
    • Marked as answer by NSG12 Thursday, January 22, 2009 10:50 PM
    Thursday, January 22, 2009 10:47 PM

All replies

  • Hi,

    You will need to break this down into several queries one would think.

    IF Exists is likely to be a help in contructing your logic.

    Something along the lines of.......

    IF exists(select Table1.ID from Table1 inner join Table2 on Table1.ID=Table2.ID)
    begin
    --action to be implemented
    end
    Else
    begin
    --Some alternative action
    end



    John Sansom (MCITP) | Please mark answers that solve your problem | www.johnsansom.com
    • Proposed as answer by John Sansom Friday, January 23, 2009 7:10 AM
    Thursday, January 22, 2009 6:19 PM
  • It would be nice and helpful , if somebody could help me to get a proc or query for this.
    Thanks.
    NSG12
    Thursday, January 22, 2009 9:56 PM

  • INSERT Table1
    SELECT * FROM Table2 t2
    WHERE NOT EXISTS
       (SELECT 1 FROM Table1 t1 WHERE
        t1.unitnum=t2.unitnum
        AND t1.CodeDesc=t2.CodeDesc
        and    t1.Code=t2.Code
        and t1.CodeType=t2.CodeType)   


    UPDATE Table1
    SET CodeType = t2.CodeType
    FROM Table2 t2
    INNER JOIN Table1 t1 ON
        t1.unitnum=t2.unitnum
        AND t1.CodeDesc=t2.CodeDesc
        and and    t1.Code=t2.Code

    DELETE FROM table1 WHERE unitnum  not IN
    (SELECT t2.unitnum FROM table2 t2 WHERE
        table1.unitnum=t2.unitnum
        AND table1.CodeDesc=t2.CodeDesc
        and    table1.Code=t2.Code
        and table1.CodeType=t2.CodeType)



    • Proposed as answer by Mark Steinberg Thursday, January 22, 2009 10:47 PM
    • Marked as answer by NSG12 Thursday, January 22, 2009 10:50 PM
    Thursday, January 22, 2009 10:47 PM