Using Update with Select

  • The Problem:

    1. I have say 20 records for a given Purchase Order in Purchase Order Item table.

    2. I want to develop sum of ProductAmt, DiscountAmt, TaxAmount and update a record(already existing in POSumTable).

    How do I do it?

    I tried the following:

    update POSumTable

    set ProductTotal = sum(b.Productamt),

         DiscountTotal = sum(b.DiscountAmt)

         TaxTotal = sum(b.TaxAmount)

    from ItemTable as b where b.POno= 23

    But, it does not seem to be working.

     

    Please guide.

    Regards,

    Dilip Nagle

     

  • Update POSumTable

    set ProductTotal = SumOfAmount,

         DiscountTotal = SumOfDiscount,

         TaxTotal = SumOfTax

    FROM (SELECT POno, sum(b.Productamt) AS SumOfAmount, sum(b.DiscountAmt) AS SumOfDiscount, sum(b.TaxAmount) AS SumOfTax

       FROM ItemTable

       GROUP BY PONo

       ) SumOfProducts

    WHERE SumOfProducts.POno = POSumTable.POno

    You can add an additional filter in the where clause to limit it to updating only one product.

    Please give this a  try and see if it works, if not, please post table structure and example data.

    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
  • -- try following

    UPDATE A

    SET ProductTotal = SUM(B.Productamt), DiscountTotal = SUM(B.DiscountAmt), TaxTotal = SUM(B.TaxAmount)

    FROM POSumTable A INNER JOIN ItemTable B ON A.PONo = B.PONo

    WHERE A.PONo= 23

  • Server: Msg 157, Level 15, State 1, Line 2

    An aggregate may not appear in the set list of an UPDATE statement.

    I thought of that first too, but a quick test proved it wouldn't work. That's why I used a subquery.

    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

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

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