Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 220
Kuthay Gumus Avatar asked Feb 06 '13 21:02

Kuthay Gumus


People also ask

Can we use aggregate functions in UPDATE statement?

An aggregate may not appear in the set list of an UPDATE statement. But SQL doesn't always agree that it should be simple. Let's setup a contrived example using a data set of Airport Gate information from San Francisco Airport. I have 1 table which has all of the times a Gate has been used at the airport.

When you have aggregate functions in a SELECT statement?

SQL provides several aggregate functions for making calculations based on columns: AVG , MAX / MIN , SUM , and COUNT . You can use those by typing the function names, then putting the column name or * inside parentheses.

Can aggregate functions be used in with clause?

An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group. If the expression includes more than one column name, each column name must be a correlated reference to the same group.


1 Answers

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
like image 113
Lance Avatar answered Sep 20 '22 21:09

Lance