Solved

TSQL update query syntax

Posted on 2014-04-16
1
248 Views
Last Modified: 2014-04-19
Hello,

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

Open in new window

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?
0
Comment
Question by:soozh
1 Comment
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 40003680
Hi,


Try this:


;WITH MyCTE AS
(
    SELECT  fd.PersonId, 
                  fd.id, 
                  fd.VisitDate,
                  ROW_NUMBER() OVER(PARTITION BY fd.PersonId ORDER BY fd.VisitDate DESC) AS RowNum
    FROM   dbo.Trombos_FollowUpData fd 
)

UPDATE    #Patients 
SET           LatestVisit = h.VisitDate, 
                 Id = h.id
FROM       #Patients P
                 JOIN MyCTE h
                     ON h.Personid = P.PersonId
WHERE    RowNum = 1

Open in new window


The above will create a CTE which will have an integer column in each row. This column(RowNum) will have an increasing value for each Patient based on the visit date. The latest Visitdate for Patient 1 would have a RowNum 1 and the latest VisitDate for patient 2 will also have RowNum = 1. This way you can get the latest Visit Date for each Patient and then use it to update accordingly.

Giannis
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now