60

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Mark Clancy
  • 7,831
  • 8
  • 43
  • 49

7 Answers7

87

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
JonH
  • 32,732
  • 12
  • 87
  • 145
  • 3
    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 '12 at 15:46
10

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;
Muhammad Omar ElShourbagy
  • 6,718
  • 2
  • 32
  • 50
4

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
C-Pound Guru
  • 15,967
  • 6
  • 46
  • 67
2

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
Ashish Gupta
  • 14,869
  • 20
  • 75
  • 134
  • @Ashish Gupta - Looks a lot like my query...hmm – JonH Mar 23 '10 at 17:24
  • 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 '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. – Ashish Gupta Mar 23 '10 at 17:31
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
MartW
  • 12,348
  • 3
  • 44
  • 68
1

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
Yoric
  • 1,761
  • 2
  • 13
  • 15
0

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
cmsjr
  • 56,771
  • 11
  • 70
  • 62