SQL Server 2008.
I have a table of patients called Patients where each patient is identified by PatientId.
I have a table of visits called Visits where each visit has the PatientId and a the visit date. There is also a unique id column in the Visit table.
I want to search the Visits table, look up the latest visit date and id for each patient in the Patients table, and update the Patient table with this values.
I am trying to use the following general syntax:
update #Patients set LatestVisit = h.VisitDate, Id = h.id from
(select top 1 fd.PersonId, fd.id, fd.VisitDate from dbo.Trombos_FollowUpData fd
order by fd.Visitdate desc ) h
where h.Personid = #Patients.PersonId
but i see i need to add a where statement in the inner select somehow to match the patients.
Maybe i need to do this using an inner join?