SQL Update to the SUM of its joined values

Question

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?

Answer 1

How about this:

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

Answer 2

An alternate to the above solutions is using Aliases for Tables:

UPDATE T1 SET T1.extrasPrice = (SELECT SUM(T2.Price) FROM BookingPitchExtras T2 WHERE T2.pitchID = T1.ID) FROM BookingPitches T1;