Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams 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?
                what are you trying to do?  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, 2010 at 17:22
                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, 2012 at 15:46
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;
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
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
     BookingPitchExtras
  GROUP BY PitchID
ON t.PitchID = p.ID
                ooops...sorry..Want me to delete the answer? I didnot load the answer when I was writing the query and was reading that article.
– Ashish Gupta
                Mar 23, 2010 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, 2010 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.
– Ashish Gupta
                Mar 23, 2010 at 17:31
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
With postgres, I had to adjust the solution with this to work for me:
UPDATE BookingPitches AS p
SET extrasPrice = t.sumPrice
        SELECT PitchID, SUM(Price) sumPrice
        FROM BookingPitchExtras
        WHERE [required] = 1
        GROUP BY PitchID 
WHERE t.PitchID = p.ID AND p.bookingID = 1
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.