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

User · Accepted Answer

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

User · Answer

With postgres  I had to adjust the solution with this to work for me   UPDATE BookingPitches AS p SET extrasPrice   t sumPrice FROM                SELECT PitchID  SUM Price  sumPrice         FROM BookingPitchExtras         WHERE  required    1         GROUP BY PitchID        t WHERE t PitchID   p ID AND p bookingID   1

User · Answer

This is a valid error  See this  Following  and others suggested below  are the ways to achieve this -  UPDATE P  SET extrasPrice   t TotalPrice FROM BookingPitches AS P INNER JOIN      SELECT     PitchID      SUM Price  TotalPrice   FROM      BookingPitchExtras   GROUP BY PitchID     t ON t PitchID   p ID

User · Answer

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

User · Answer

Use a sub query similar to the below     UPDATE P SET extrasPrice   sub TotalPrice from BookingPitches p inner join   Select PitchID  Sum Price  TotalPrice     from  dbo BookingPitchExtras     Where  Required    1     Group by Pitchid   as Sub on p Id   e PitchId  where p BookingId   1

User · Answer

I ran into the same issue and found that I could solve it with a Common Table Expression  available in SQL 2005 or later     with cte as       SELECT PitchID  SUM Price  somePrice     FROM BookingPitchExtras     WHERE  required    1      GROUP BY PitchID  UPDATE p SET p extrasPrice cte SomePrice FROM BookingPitches p INNER JOIN cte ON p ID cte PitchID WHERE p BookingID 1

User · Answer

You need something like this    UPDATE P SET ExtrasPrice   E TotalPrice FROM dbo BookingPitches AS P INNER JOIN  SELECT BPE PitchID  Sum BPE Price  AS TotalPrice     FROM BookingPitchExtras AS BPE     WHERE BPE  Required    1     GROUP BY BPE PitchID  AS E ON P ID   E PitchID WHERE P BookingID   1

[sql] SQL Update to the SUM of its joined values

Examples related to sql

Examples related to sum

Examples related to sql-update