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)

Answer 2

You need an intermediate step to associate the Max(modified_date) to each employee_id.

Using a CTE expression you can try something like this :

with
more_recent as (
    select
        employee_id,
        Max(modified_date) max_modified_date
    from
        table2
    group by
        employee_id    
)
update
    t1
set
    t1.employee_name = t2.employee_name
from
    table1 as t1
    inner join more_recent mr on 
        t1.employee_id = mr.employee_id
    inner join table2 as t2 on 
        mr.employee_id = t2.employee_id
        mr.max_modified_date = t2.modified_date