Update with group by

Transact-SQL
1

I have the following :

     UPDATE snapshot2
     SET oppvirkulttotaal = result
    (SELECT kultivar, SUM(opp) as result
    FROM Snapshot
    GROUP BY kultivar)

I want to upate column oppvirtotaal in snapshot2 with the sum in snapshot (column opp) were kultivar as the same in both tables.

When I run the statement from (SELECT ..... it works fine . The problem seems to be with my update statement.

Regards

2

One of these might work for you:

update s1
   set s1.oppvirkulttotaal=s2.result
  from snapshot2 as s1
       inner join (select kultivar
                         ,sum(opp) as result
                     from snapshot
                    group by kultivar
                  ) as s2
               on s2.kultivar=s1.kultivar
;

or

update s1
   set s1.oppvirkulttotaal=(select sum(opp)
                              from snapshot as s2
                             where s2.kultivar=s1.kultivar
                           )
  from snapshot2 as s1
;
1 Like
3

Thank you bitsmed.It worked.