Search code examples
sqlsumsql-update

SQL Update to the SUM of its joined values

I'm trying to update a field in the database to the sum of its joined values:

UPDATE P
SET extrasPrice = SUM(E.price)
FROM dbo.BookingPitchExtras AS E
INNER JOIN dbo.BookingPitches AS P ON E.pitchID = P.ID
    AND P.bookingID = 1
WHERE E.[required] = 1

When I run this I get the following error:

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

Any ideas?

Solution

  • How about this:

    UPDATE 
     p
SET 
     p.extrasPrice = t.sumPrice
FROM 
     BookingPitches AS p
INNER JOIN
    (
        SELECT 
             PitchID, 
             SUM(Price) AS sumPrice
        FROM 
             BookingPitchExtras
        WHERE 
             [required] = 1
        GROUP BY 
             PitchID 
    ) t
ON 
     t.PitchID = p.ID
WHERE 
     p.bookingID = 1;