SQL Update to the SUM of its joined values?

Up vote 3 down vote favorite share g+ share fb share tw.

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?

Sql update sum link|improve this question edited Mar 23 '10 at 17:25RedFilter50.2k24698 asked Mar 23 '10 at 17:13CL4NCY702925 91% accept rate.

Are you trying to update some dbo. BookingPitches records with a sum of all the corresponding dbo. BookingPitcheExtras records price column?

– Patrick Karcher Mar 23 '10 at 17:22 You need to specify which field you want to group on if you are going to use SUM. – FrustratedWithFormsDesigner Mar 23 '10 at 17:22 This statement doesn't make any sense. What exactly is it you're trying to do?

– Ian Henry Mar 23 '10 at 17:22.

How about this: UPDATE P SET extrasPrice = t. SomePrice FROM BookingPitches AS P INNER JOIN ( SELECT PitchID, SUM(Price) somePrice FROM BookingPitchExtras WHERE required = 1 GROUP BY PitchID ) t ON t. PitchID = p.

ID WHERE P. BookingID = 1.

I used this syntax today as a guide in shaping my update statement, worked like a charm. As a side note, make sure to use the alias values exactly as you see them here. I didn't at first and spent a little while trying to figure out my issue.

– Dylan Hayes Jan 23 at 15:46.

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.

I didnot load the answer when I was writing the query and was reading that article. – ydobonmai Mar 23 '10 at 17:25 No that is fine I just thought it was odd that we even used the same temp storage t, and the query was quite almost exactly the same. 2 answers are better then 1.

– JonH Mar 23 '10 at 17:28 JonH, I will learn to load the answers while I am writing answers. Sorry again. Didn't mean that.

I edited my answer as well. – ydobonmai Mar 23 '10 at 17:31.

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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.