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?

Endorsement

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....

Quadricycle

Yep, I simplified the actual query so syntax wasn't correct. Good answer, I'll check tomorrow and give you the tick. Just a question though, in the original query, is it just random which row will update the UserAssesment table? Does it's value get updated multiple times? Interested to know the default behavior. – Endorsement

Yes it will be updated multiple times but there is no guarantee in which order very error prone approach. You will be left with the last value it gets updated by. – Quadricycle

Answer 2

Here is where the good old subquery works, and is very simple:

UPDATE UserAssesment set AssessmentDate =
    (select top 1 comp.AssessmentDate 
    from vw_CompletedAssessments comp 
    where ua.NatId = comp.NatId and
          ua.FamilyName = comp.ClientLastName and
          ua.GivenName = comp.ClientFirstName
    order by comp.date desc
    )
WHERE ua.HasCompletedAssessment <> 0

Sparrow

Answer 3

If more than one match is found, then all will be updated.

If you want only one to be updated, can you use UPDATE TOP (1)?

If you want to guarantee the order of the one to be updated, try to add an appropriate ORDER BY clause.

Brahma

I want all of the UserAssesment records to be updated. I mean how do the records get updated if there are multiple entries in vw_CompletedAssessments? Also, I can't see Order By in the Update documentation. How would it be used? – Endorsement

Does https://mcmap.net/q/299123/-sql-server-update-a-table-by-using-order-by or maybe more appropriate https://mcmap.net/q/299123/-sql-server-update-a-table-by-using-order-by help? – Brahma