January 25, 2010 at 4:26 am
Employee table i have following reords
Employeeid totalSalay
1 0
2 0
3 0
Employee salary table having following records
Eployeeid month salary
1 1 5000
1 2 6000
2 1 7000
2 2 6500
how can i update the employee table in a single update command ?
the output should be
Employeeid totalSalay
1 11000
2 13500
how to achive this ?
3 0
January 25, 2010 at 5:25 am
Assuming table names to be Employee and Salary.
Update Employee set TotalSalary = (Select sum(Salary) from Salary where EmployeeID = Employee.EmployeeID)
or
Update Employee set TotalSalary = a.TotalSalary
from (Select EmployeeID, SUM(Salary) as TotalSalary from Salary group by EmployeeID)
as a
where a.EmployeeID = id
January 25, 2010 at 5:49 am
thank you
January 25, 2010 at 6:00 am
Please beware that a correlated subquery is an outer join.
If you have a value in target table before, and the correlated subquery doesn't return any value, the target table value is replaced with NULL.
N 56°04'39.16"
E 12°55'05.25"
January 25, 2010 at 7:07 am
SwePeso is right and I should I mentioned this, the first example will put a null value in all the records that don't have rows for the same employee in the salary table. This code clearly shows it.
Declare @Employee Table
(
EmployeeID int,
TotalSalary int
)
Insert into @Employee values (1,0), (2,0), (3,0)
Select * from @Employee
Declare @Salary Table
(
EmployeeID int,
Month int,
Salary int
)
Insert into @Salary values (1,1,5000), (1,2,6000), (2,1,7000),(2,2,6500)
Update@Employee
SetTotalSalary =
(
Selectsum(Salary)
from@Salary
whereEmployeeID = [@Employee].EmployeeID)
Select * from @Employee
The result is:
EmployeeID TotalSalary
----------- -----------
1 11000
2 13500
3 NULL
The second example however doesn't have this problem because it specifically updates only the row in Employee for which a record exists in the sub-query result.
Declare @Employee Table
(
EmployeeID int,
TotalSalary int
)
Insert into @Employee values (1,0), (2,0), (3,0)
Select * from @Employee
Declare @Salary Table
(
EmployeeID int,
Month int,
Salary int
)
Insert into @Salary values (1,1,5000), (1,2,6000), (2,1,7000),(2,2,6500)
Update@Employee
set TotalSalary = a.TotalSalary
from (Select EmployeeID, SUM(Salary) as TotalSalary
from @Salary group by EmployeeID)
as a
where a.EmployeeID = [@Employee].EmployeeID
Result:
EmployeeID TotalSalary
----------- -----------
1 0
2 0
3 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply