Update Statements With Aggregates

ASE allows aggregated functions in the ‘Set’ clause of an update statement. MS SQL Server does not allow this.

Example

create table Example (a int, b int)

insert into Example values (1, 0)

insert into Example values (2, 0)

insert into Example values (3, 0)

go

-- This statement will not work on MS SQL Server

update Example set b = sum(a)

go

select * from Example

drop table Example

Note: Use a construction with a subquery or an inline view on MS SQL Server.

-- With a subquery:

update Example set b = (select sum(a) from Example)

-- With an inline view (also called ‘derived table’):

update Example set b = E.SumA

from (select Sum(a) as SumA from Example) as E