UPDATE row from query with multiple matches

Question

Given an update statement like so:

UPDATE
     UserAssesment
SET
     AssessmentDate = comp.AssessmentDate     
FROM
     UserAssesment ua
INNER JOIN 
    vw_CompletedAssessments comp
On 
    ua.NatId = comp.NatId and
    ua.FamilyName = comp.ClientLastName and
    ua.GivenName = comp.ClientFirstName

WHERE
     ua.HasCompletedAssessment <> 0

if a user can have multiple records that match the join clause to vw_CompletedAssessments, which record will be used for the update? Is there a way to order it so the max or min AssessmentDate is used?

Answer 1

Your Syntax for UPDATE needs some tweaking see below:

UPDATE ua
SET
     ua.AssessmentDate = comp.AssessmentDate     
FROM  UserAssesment ua
INNER JOIN  vw_CompletedAssessments comp
ON  ua.NatId = comp.NatId and
    ua.FamilyName = comp.ClientLastName and
    ua.GivenName = comp.ClientFirstName
WHERE ua.HasCompletedAssessment <> 0

Now coming to the point if you have multiple values and you want to Pick a particular value from Comp table for that you can make use of ROW_NUMBER functions something like this...

UPDATE ua
SET
     ua.AssessmentDate = comp.AssessmentDate     
FROM  UserAssesment ua
INNER JOIN  (SELECT * 
            , ROW_NUMBER() OVER (PARTITION BY NatId ORDER BY AssessmentDate DESC) rn 
            FROM vw_CompletedAssessments) comp
ON  ua.NatId      = comp.NatId 
and ua.FamilyName = comp.ClientLastName 
and ua.GivenName  = comp.ClientFirstName
WHERE ua.HasCompletedAssessment <> 0
AND Comp.rn = 1

This query will update the ua.AssessmentDate to the latest comp.AssessmentDate for a particular NatId. Similarly you can see how you can manipulate the results using row number. If you want to update it to the oldest comp.AssessmentDate value just change the order by clause in row_number() function to ASC and so on....