Post reply

Updating a Column value by addition of values from another column in another table

  • January 26, 2017 at 11:38 am

    #317849

    Hello 

    I have the table below: Table  A
    ContractDetailId           OrderCopyHeaderID     RotationCount
    207534                              15421977                  3
    370304                               15422035                11
    127316                               15422035                6

    Matching column in both tables is 'OrderCopyHeaderID',

    Table B

    ContractDetailId           OrderCopyHeaderID     RotationCount
    207534                              15421977                  3146
    370304                               15422035                11234
    127316                               15422035                6543

    i want to update table B by adding values(RotationCount) from table A 

    Please help.

    Thanks

    I learn from the footprints of giants......

  • January 26, 2017 at 11:51 am

    #1925146

    you can do a join in an UPDATE statement, just reference the alias of the table you want to update, something like this:

    UPDATE b SET RotationCount += a.RotationCount
      FROM TableB b
      INNER JOIN TableA a ON b.OrderCopyHeaderID = a.OrderCopyHeaderID

  • January 26, 2017 at 11:54 am

    #1925148

    Chris Harshman - Thursday, January 26, 2017 11:51 AM

    you can do a join in an UPDATE statement, just reference the alias of the table you want to update, something like this:

    UPDATE b SET RotationCount += a.RotationCount
      FROM TableB b
      INNER JOIN TableA a ON b.OrderCopyHeaderID = a.OrderCopyHeaderID

    UPDATE OrderCopyDetail SET RotationCount = ISNULL(OCD.RotationCount, 0) + IT.RotationCount
        From #InventoryTable2 IT Inner Join OrderCopydetail OCD On IT.OrderCopyHeaderID=OCD.OrderCopyHeaderID
        where IT.OrderCopyHeaderID=OCD.OrderCopyHeaderID

    This is what i did. im about to test this,. thanks

    I learn from the footprints of giants......

  • January 26, 2017 at 12:06 pm

    #1925153

    looks good that you're handling NULL, the only change I'd make is reference OCD instead of the full table name, and the WHERE clause is redundant with your JOIN condition:

    UPDATE OCD SET RotationCount = ISNULL(OCD.RotationCount, 0) + IT.RotationCount
    FROM #InventoryTable2 IT Inner Join OrderCopydetail OCD On IT.OrderCopyHeaderID=OCD.OrderCopyHeaderID

  • January 26, 2017 at 12:24 pm

    #1925163

    Chris Harshman - Thursday, January 26, 2017 12:05 PM

    looks good that you're handling NULL, the only change I'd make is reference OCD instead of the full table name, and the WHERE clause is redundant with your JOIN condition:

    UPDATE OCD SET RotationCount = ISNULL(OCD.RotationCount, 0) + IT.RotationCount
    FROM #InventoryTable2 IT Inner Join OrderCopydetail OCD On IT.OrderCopyHeaderID=OCD.OrderCopyHeaderID

    Thanks a lot.

    I learn from the footprints of giants......

Viewing 5 posts - 1 through 4 (of 4 total)

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