Solved

TSQL update query syntax

Posted on 2014-04-16
1
247 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

10 Experts available now in Live!

Get 1:1 Help Now