[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

TSQL update query syntax

Posted on 2014-04-16
1
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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