如何使用UPDATE JOIN从多行更新单行

EJoshuaS-恢复莫妮卡

我试图在SQL Server 2016中使用UPDATE JOIN从多行更新单行。

我有一个表，其中包含许多审核事件，看起来像：

Event_Date,Event_Name,Entity_ID
2020-01-01,'USER_ROLE_CHANGED',12345
2020-01-02,'USER_ACCOUNT_ACTIVATED',12345
2020-01-03,'USER"ACCOUNT_DEACTIVATED',12345

User表如下所示：

Employee_ID,Employee_Name,Date_Activated,Date_Deactivated,Date_Role_Assigned
12345,'Joe Cool',null,null,null
....

我想更新此记录，使其看起来像这样：

Employee_ID,Employee_Name,Date_Activated,Date_Deactivated,Date_Role_Assigned
12345,'Joe Cool',2020-01-02,2020-01-03,2020-01-01
.....

这是我尝试过的：

update u
set u.date_role_assigned = iif(a.event_name = 'USER_ROLE_CHANGED', a.event_date, u.date_role_assigned),
u.date_activated = iif(a.event_name = 'USER_ACCOUNT_ACTIVATED', a.event_date, u.date_activated),
u.date_deactivated = iif(a.event_name = 'USER_ACCOUNT_DEACTIVATED', a.event_date, u.date_deactivated)

from dbo.[User] u
inner join dbo.AuditEventsPivot a on a.entid = u.empid

但是，我实际上得到了以下内容：

Employee_ID,Employee_Name,Date_Activated,Date_Deactivated,Date_Role_Assigned
12345,'Joe Cool',2020-01-02,null,null
.....

换句话说，它仅更新了其中一列，而未更新其他两列。（顺便说一句，我确实验证了有关ID的表中是否存在所有三个事件，所以这不是问题-问题在于我编写联接本身的方式）。

我可以通过将其拆分为多个查询来解决此问题，但如果可能的话，我希望使用单个查询来提高性能并简化代码。（如果这实际上不会提高性能，请随时纠正我）。

这是可能做到的，还是我毕竟必须将其拆分为多个查询？

我已经看到了几个相关的问题，但是没有一个直接解决我的问题。例如，这一行询问如果JOIN子句匹配多行会发生什么。基于这一点（以及类似的问答），我的理解是，您无法“算”到哪一个。但是，我不是要更新哪一个，而是要全部更新。

我找到了一个尝试对子查询执行此操作的问答，但这不是我想要的，因为我想使用UPDATE JOIN而不是子查询。

戈登·利诺夫

一种方法是预聚合：

update u
    set date_role_assigned = coalesce(user_role, u.date_role_assigned)
u.date_role_assigned),
        date_activated = coalesce(a.user_account_activated, u.date_activated),
        date_deactivated = coalesce(a.user_account_activated, u.date_deactivated)
from dbo.[User] u join
     (select a.entid,
             max(case when a.event_name = 'USER_ROLE_CHANGED' then a.event_date end) as user_role,
             max(case when a.event_name = 'USER_ACCOUNT_ACTIVATED' then a.event_date end) as user_account_activated,
             max(case when a.event_name = 'USER_ACCOUNT_DEACTIVATED' then a.event_date end) as user_account_deactivated
      from dbo.AuditEventsPivot a 
      group by a.entid
     ) a
     on a.entid = u.empid;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。