Post reply

Update from SUM query

  • December 5, 2005 at 3:30 am

    #152650

    Hi,

    I'd like to be able to update a header table from the corresponding sum in the detail table:

    ie :

    update order_header set order_header.total_weight = sum(order_lines.weight) from order_lines where order_header.order_number = order_lines.order_no

    Hope this makes sense,

    Thanks in advance,

    Mark 

  • December 5, 2005 at 4:16 am

    #607640

    update order_header set order_header.total_weight = TotalOrder.TotalWeight from (SELECT order_no, sum(weight) AS TotalWeight FROM order_lines GROUP BY order_no) TotalOrder where order_header.order_number= TotalOrder.order_no

    Not tested, but should be more-or-less right.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 5, 2005 at 4:37 am

    #607644

    GilaMonster - PERFECT!

    Thanks very much

    Mark

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

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