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"
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)