The general format for doing an UPDATE based on a JOIN similar to doing a SELECT; I have used this format with both INNER and OUTER joins.
UPDATE d
SET d.DestValue1 = s.SourceValue1
FROM DestinationTable d
JOIN SourceTable s ON d.TableIndex = s.TableIndex
It will be up to you to get your table mappings straight
Update
As
Maciej Los
pointed out in the other reply, you do have some issues with your syntax that should be taken care of
CASE
WHEN EmployeesAttendance.Hours >= 8 THEN 1
WHEN EmployeesAttendance.Hours = 0 THEN 0
WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 Day,
CASE
WHEN EmployeesAttendance.Hours > EmployeeDetails.Dhour then EmployeesAttendance .Hours - EmployeesAttendance .Dhour
else 0
End as OT,
There is no
END
to the first statement.
The results will be null between 0 and 6 hours.
I also generally recommend against using
Reserved/Special Words as column names; if they must be used then they should be wrapped with
[]
(eg days, hours). My rule of thumb is if a column or variable name changes color in either C# or TSQL, change the name.
I ran the following statements and you can see how this works out with some real data
declare @EmployeeAttendance table(id int identity(1,1), [hours] decimal(9,2) )
insert @EmployeeAttendance values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
SELECT ID, [HOURS],
CASE
WHEN EmployeesAttendance.[Hours] >= 8 THEN 1
WHEN EmployeesAttendance.[Hours] = 0 THEN 0
WHEN EmployeesAttendance.[Hours] >= 6 THEN 0.5
END AS [Day]
FROM @EmployeeAttendance EmployeesAttendance
ORDER BY 1
Which returned the following
ID HOURS Day
1 0.00 0.0
2 1.00 NULL
3 2.00 NULL
4 3.00 NULL
5 4.00 NULL
6 5.00 NULL
7 6.00 0.5
8 7.00 0.5
9 8.00 1.0
10 9.00 1.0
11 10.00 1.0
So it looks like you still have some work to do