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
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
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: