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

UPDATE row from query with multiple matches(从具有多个匹配项的查询更新行)

问题描述

给定一个像这样的更新语句:

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

如果用户可以有多个记录与vw_CompletedAssessments 的join 子句匹配，那么将使用哪个记录进行更新?有没有办法订购它以便使用最大或最小 AssessmentDate ?

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?

推荐答案

你的 UPDATE 语法需要一些调整，见下文:

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

如果您有多个值并且想要从 Comp 表中选择一个特定的值，您可以使用 ROW_NUMBER 这样的函数...

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

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

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

这篇关于从具有多个匹配项的查询更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！