更新查询中具有多个匹配项的行

给出如下更新语句：

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

如果用户可以有多个与 join 子句匹配的记录vw_CompletedAssessments，哪条记录将用于更新？有没有办法订购它，以便最大或最小AssessmentDate用来？

您的 UPDATE 语法需要一些调整，如下所示：

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

现在，如果您有多个值并且想要从 Comp 表中选择一个特定值，您可以使用 ROW_NUMBER 函数，如下所示...

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

此查询会将 ua.AssessmentDate 更新为特定 NatId 的最新 comp.AssessmentDate。同样，您可以看到如何使用行号来操作结果。如果您想将其更新为最旧的 comp.AssessmentDate 值，只需将 row_number() 函数中的 order by 子句更改为 ASC 等等......