asked on

SQL Update Statement using aggregate

I need to update a field in table pocalc from data from the same record

UPDATE POCALC
SET Pocalc.max =ceiling(SUM(slspd01+slspd02+slspd03+slspd04+slspd05+slspd06+slspd07+slspd08+slspd09+slspd10+slspd11+slspd12)/12)
WHERE (SiteID = 'west') AND (InvtID LIKE 'fri%')

I get the following error:
An aggregate may not appear in the set list of an UPDATE statement.

I was wondering if someone could point me in the right direction to reformatting this.

Barry Cunney

USE A SUBQUERY To get the value to fed into the 'SET = '

UPDATE POCALC
SET Pocalc.max =
( SELECT ceiling(SUM(slspd01+slspd02+slspd03+slspd04+slspd05+slspd06+slspd07+slspd08+slspd09+slspd10+slspd11+slspd12)/12) )
WHERE (SiteID = 'west') AND (InvtID LIKE 'fri%')

armgon

ASKER

thanks for the assistance.

unfortunately with the subquery I am getting this error:
Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.

Barry Cunney

UPDATE p
SET p.max = agg.Total
FROM POCALC p
INNER JOIN (
SELECT ID, ceiling(SUM(slspd01+slspd02+slspd03+slspd04+slspd05+slspd06+slspd07+slspd08+slspd09+slspd10+slspd11+slspd12)/12) ) Total
FROM POCALC p1
GROUP BY ID
) AS agg
ON p.ID = agg.ID

armgon

ASKER

I am getting this error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.

x-men

DECLARE @max float
set @max = select ceiling(SUM(slspd01+slspd02+slspd03+slspd04+slspd05+slspd06+slspd07+slspd08+slspd09+slspd10+slspd11+slspd12)/12) FROM POCALC

UPDATE POCALC
SET Pocalc.max = @max
WHERE (SiteID = 'west') AND (InvtID LIKE 'fri%')

armgon

ASKER

X-men: The following error is returned with the query

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.

SOLUTION