CODEWITHSUNDEEP
sqlms-accessms-access-2010ms-access-2007aggregate-functions
emilios
emilios

Reputation: 375

Update with aggregate function SUM

I have the following SQL statement working fine:

SELECT  SUM(IIF(srsFromStock, srsAmount, 0)), srvID
FROM tblServices AS S INNER JOIN tblServicesSub AS SS ON S.srvID =
SS.srssrvID 
GROUP BY srvID

I want to transform this select to an update but I haven't managed to:

UPDATE tblServices AS S INNER JOIN tblServicesSub AS SS ON S.srvID =
SS.srssrvID  SET srvSumFromStock = SUM(IIF(srsFromStock, srsAmount,
0)) GROUP BY srvID

What is happening and how can I fix it?

Upvotes: 0

Views: 69

Answers (1)

Shukri Gashi
Shukri Gashi

Reputation: 535

You can't update using aggregate values. If I wold be in this situation I wold use table variable or temp table to save aggregate result and then join tables and update them. Somthing like this:

   DECLARE @tempTbl TABLE (srssrvID INT,aggregate_sum DECIMAL(18,4))
   INSERT INTO @tempTbl SELECT srssrvID,SUM(IIF(srsFromStock, srsAmount, 0)) FROM tblServicesSub GROUP BY srssrvID
   UPDATE tblServices INNER JOIN @tempTbl ON srssrvID=srvID
   SET srvSumFromStock=aggregate_sum

Upvotes: 2

Related Questions