?
Solved

TSQL update query syntax

Posted on 2014-04-16
1
Medium Priority
?
266 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

593 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