none
update with sum

    Question

  • i want to sum up the values during the update process but error raises


    SCRIPT:

    UPDATE T1

    SET ParentBandStd = SUM(T3.NettStd)

    FROM STAGING.DBO.CUSTOMERBANDINGLOAD T1 INNER JOIN PRESENTATIONEUROPE.DBO.CUSTOMER T2 ON

    T1.LOCALPARENT = T2.LOCALPARENT INNER JOIN PRESENTATIONEUROPE.DBO.SALESANDORDERS T3

    ON T2.CUSTOMER = T3.SHIPCUSTOMER

    WHERE T3.INVOICEDATE >= @STARTDATE AND T3.INVOICEDATE <= @CURRENTDATE AND T3.TRANSTYPE = 'L'

     

    Error:

    Msg 157, Level 15, State 1, Line 21

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

    Msg 157, Level 15, State 1, Line 29

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


    any help will be appreciated

    Thursday, October 12, 2006 2:28 PM

Answers

  • You need a SELECT befor the SUM(...).

    UPDATE T1

    SET ParentBandStd = (SELECT SUM(T3.NettStd)

    FROM STAGING.DBO.CUSTOMERBANDINGLOAD T1 INNER JOIN PRESENTATIONEUROPE.DBO.CUSTOMER T2 ON

    T1.LOCALPARENT = T2.LOCALPARENT INNER JOIN PRESENTATIONEUROPE.DBO.SALESANDORDERS T3

    ON T2.CUSTOMER = T3.SHIPCUSTOMER

    WHERE T3.INVOICEDATE >= @STARTDATE AND T3.INVOICEDATE <= @CURRENTDATE AND T3.TRANSTYPE = 'L')

    Thursday, October 12, 2006 3:07 PM