Update table1 from table2 value by recent date

Question

I wanted to update table1 based on table2 which has a common field employee_ID. I always have unique employee_ID in table1 but in table2 it may contain duplicate records with same employee_ID.

I got another column in table2 where the modified_date is inserted. I want to update the table1 employee name with the table2 employee name based on employee_id and recent modified date.

I got multiple columns to update with the same type of condition. any idea, here is so far i tried.

Here is the query, i have using inner join,

ssql = "Update Table1 INNER JOIN Table2 

ON Table1.employee_id= Table2.employee_id 

SET Table1.type= Table2.type"

any help will be appreciated

Answer 1

try this query

update t1 set t1.employee_name = t2.employee_name from table1 as t1
inner join table2 as t2 on t1.employee_id = t2.employee_id
where t2.Modified_date = (select Max(modified_date) from table2 as tb2 where 
tb2.employee_id = t2.employee_id group by tb2.employee_id)

Update table1 from table2 value by recent date

Sign up