CODEWITHSUNDEEP
sqlsql-server
Kuthay Gumus
Kuthay Gumus

Reputation: 764

An aggregate may not appear in the set list of an UPDATE statement

UPDATE [silverdb01].[dbo].[info] 
SET [FM] = SUM(a.[MONDAY] - b.[QUOTA]) 
FROM  [silverdb01].[dbo].[info] a,  [silverdb01].[dbo].[quota] b 
WHERE a.[WORK_TYPE]='IN' AND a.[NAME]='KUTHAY'

When I run this I get the following error:

An aggregate may not appear in the set list of an UPDATE statement.

Any ideas?

Upvotes: 21

Views: 25059

Answers (1)

Lance
Lance

Reputation: 3213

I am guessing that (as other's have pointed out) you don't really want a cartesian on this update so I have added an "id" to the query so you will have to do some modification but this might get you on the right path

;with temp as (
    select  a.id, SUM(a.pazartesi - b.kota) as newTotal
    from    [asgdb01].[dbo].[info] a join [asgdb01].[dbo].[kota] b 
          on a.id = b.id
    where   a.work_type='in' and a.name='alp' )
update  a
set     fm = t.newTotal
from    [asgdb01].[dbo].[info] a join temp t on a.id = t.id

Upvotes: 38

Related Questions