Menu
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?
220
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
