sql - 根据第一个表更新第二个表列

Question

我有两张桌子。一个是Employee，另一个是Department。

Employee 表有以下列：

Name | Salary | Department Id

部门表有

Department Id| Department Name | SumofSalary

我想明智地更新 SumofSalary 部门

我试过的代码

update Department set sumofsalary =
(
    select D.deptid, SUM(E.salary) from Department D inner join Employesalary E 
    on d.deptid=e.deptid group by D.deptid
)

标签： sql

Answer 1

以下将为您提供部门工资总和并在部门表中更新。

UPDATE D SET D.SumOfSalary = T.Salary FROM Department D JOIN 
(
    SELECT SUM(Salary) Salary, [Department ID] DeptID FROM Employee Group By [Deptartment ID]
) T ON T.DeptID = D.[Department ID]