SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT EmployeeId, Status, Department, CurrentRecord, EmployeeVersion
INTO #A
FROM(
VALUES
  (45  ,      1  ,   33    ,    0      ,       1),
  (45  ,      1  ,   45    ,    0      ,       2),
  (45  ,      2  ,   45    ,    0      ,       3)
)V(EmployeeId, Status, Department, CurrentRecord, EmployeeVersion)
3 rows affected

; WITH cte as(
 select EmployeeId, max(EmployeeVersion) as EmployeeVersion
 from #A
 group by EmployeeId
)
update a
set CurrentRecord = 1
from #A a
inner join cte on a.EmployeeId = cte.EmployeeId and a.EmployeeVersion = cte.EmployeeVersion
1 rows affected

select * from #A


EmployeeId
Status
Department
CurrentRecord
EmployeeVersion


45
1
33
0
1

45
1
45
0
2

45
2
45
1
3