Scott Eremia-Roden Scott Eremia-Roden - 2 months ago 8
SQL Question

Update a table with the most current information from another table

I have two tables that are related by an employee ID

T1 (

DateOfLatestChange
EmployeeID

)

T2 (

EmployeeID
DateOfChange
CommentsOfChange

)

T2 will update frequently and I want T1 to hold the DateOfChange values of the latest value. I am looking to do this in a stored procedure without passing the EmployeeID parameters. What is the best way to update this table? Thanks.

Answer

could be as simple as

UPDATE t1
SET t1.DateOfChange = t2.DateOfChange
FROM t1 AS t1
JOIN (SELECT
  EmployeeID, max (DateOfChange) DateOfChange
FROM t2
GROUP BY EmployeeID) AS t2
  ON t1.EmployeeID = t2.EmployeeID