Post reply

Update Statment Issue, Looking For Optimized solution

  • September 24, 2009 at 4:53 am

    #211429

    Hello Friends,

    I am looking for optimized solution, to update the values from one table to another table. Currently I am executing the update statement into the Courser. Actual data is huge, want to update millions of records. Please see the simulated code below,

    I have two tables TABLE_1 and TABLE_2

    DECLARE @TABLE_1 TABLE

    (

    Code_1 VARCHAR(10),

    Value_1 VARCHAR(10)

    )

    DECLARE @TABLE_2 TABLE

    (

    Code_2 VARCHAR(10),

    Value_2 VARCHAR(10)

    )

    INSERT @TABLE_1

    Select 'PO_1', 'VAL_1' UNION ALL

    Select 'PO_2', 'VAL_2' UNION ALL

    Select 'PO_3', 'VAL_3' UNION ALL

    Select 'PO_4', 'VAL_4' UNION ALL

    Select 'PO_5', 'VAL_5' UNION ALL

    Select 'PO_6', 'VAL_6' UNION ALL

    Select 'PO_7', 'VAL_7' UNION ALL

    Select 'PO_8', 'VAL_8' UNION ALL

    Select 'PO_9', 'VAL_9' UNION ALL

    Select 'PO_10', 'VAL_10' UNION ALL

    Select 'PO_11', 'VAL_11' UNION ALL

    Select 'PO_12', 'VAL_12' UNION ALL

    Select 'PO_13', 'VAL_13'

    INSERT @TABLE_2

    Select 'PO_1', '' UNION ALL

    Select 'PO_2', '' UNION ALL

    Select 'PO_3', '' UNION ALL

    Select 'PO_5', '' UNION ALL

    Select 'PO_6', '' UNION ALL

    Select 'PO_7', '' UNION ALL

    Select 'PO_8', '' UNION ALL

    Select 'PO_10', '' UNION ALL

    Select 'PO_11', ''

    Here I have executed update statement for single Record,

    UPDATE @TABLE_2

    SET Value_2 = (SELECT Value_1 FROM @TABLE_1 WHERE Code_1 = 'PO_1')

    WHERE Code_2 = 'PO_1'

    Same statement I have executed into the Courser .

    I have Update the millions of records,

    Is there any solution to avoid the Courser, and Update the values in single Update statement.

    Thank U,

    Jayraj

  • September 24, 2009 at 5:59 am

    #1057950

    Try this:

    update B set Value_2=Value_1 from @TABLE_2 B inner join @TABLE_1 A

    on Code_1=Code_2

  • September 24, 2009 at 6:21 am

    #1057958

    Thank you very much,

    Jayraj

Viewing 3 posts - 1 through 2 (of 2 total)

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