.NET Programming With Me

SQL Server: Aggregate Function in an Update Query

The following code shows us how to use an aggregate function while updating a table.
I've used the sum function.

update t1
set t1.fieldName1 = t2.NewFieldName
from table1 t1
inner join (select fieldName1, sum(fieldName2) as 'NewFieldName' from Table99 
where  group by fieldName1) as t2
on t1.fieldName1 = t2.fieldName1
GO

Example:
update t1
set t1.total_balance = t2.totalPay
from employee t1
inner join (select empId, sum(payment) as totalPay 
from employee_payment
where employee_payment.[year] = 2013
group by empId) as t2
on t1.empId = t2.empId
GO

No comments: