Update aggregate with conditions

Transact-SQL
1

I am using the following SQL statement to update a column for SkedTonKultKlasGereed.
I want to sum the Skedskatting where SkedslaagB = 'JA'. This must be done for each SkedkultKlas.

I have the following unsuccessful statement :

  update Skedulering r set SkedTonKultKlasGereed =
         ( select  sum(skedskatting)  from Skedulering r2
         where r2.SkedKultklas = r.Skedkultklas)
         where r.SkedslaagB = 'JA';

I get an error :
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'r'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

What am I missing?

Regards

2

if you carefully read the doc, you'll see that this is not valid syntax. generally:

update alias
set column = something
from table as alias
join .... etc.
3

The problem is you can't define the alias r like you have above, you need to use a FROM statement in the outer query:

update r set r.SkedTonKultKlasGereed =
( select  sum(r2.skedskatting)  from Skedulering r2
where r2.SkedKultklas = r.Skedkultklas)
from Skedulering r
where r.SkedslaagB = 'JA';
4

and the whole thing would read better as a join instead of a sub query off the SET

5

I think best to explain is by the below table. I want every line to update SkedkultklasGereed with sum of Skedskatting where Skedslaag = 'JA'. This should be done for every row where Skedkultklas are the same. Hope it helps. I used EXCEL Sumifs to achieve this.

Skedkulklas Skedskatting SlaagB Skedtonkulklasgereed
CHBBULK 50 JA 90
CHBSEL 60 JA 60
CHBBULK 40 JA 90
CASRES 20 NEE 40
CASRES 40 JA 40
CASKULT 60 JA 110
CASKULT 50 JA 110
CASKULT 20 NEE 110
CHBBULK 30 NEE 90
6 
update r
setr rSkedkultklasGereed  = r2.sum_skatting
from Skedulering  r
join (select sum(r2.skedskatting) as sum_skatting, r2.skedkultklas 
from sckedulering r2
where r2.SkedslaagB  = 'ja'
group by r2.skedkultklas
) r2
on r.Skedkultklas = r2.Skedkultklas
7

Thank you gbritton. That does it!