How to update a single row from multiple rows with UPDATE JOIN

Question

I am trying to use an UPDATE JOIN in SQL Server 2016 to update a single row from multiple rows.

I have a table with a bunch of audit events that looks like:

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

The User table looks like this:

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

I want to update this record to look like this:

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

Here is what I tried:

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

However, I actually got the following:

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

In other words, it only updated one of the columns and didn't update the other two. (Incidentally, I did verify that all three events exist in the table for the ID in question, so that's not the problem - the problem is with the way I've written the join itself).

I could solve this by splitting this into multiple queries, but I would prefer to use a single query if possible to improve performance and make the code more concise. (Please feel free to correct me if this would not, in fact, lead to a performance gain).

Is this possible to do, or am I going to have to split this into multiple queries after all?

I have seen several related questions, but none that directly address my problem; for example, this one is asking what will happen if the JOIN clause matches multiple rows. My understanding based on this (and similar Q&As) is that you can't "count" on which one it'll pick. However, I'm not asking which one will be updated, I'd like all of them to be updated.

I found a Q&A trying to do this with a subquery, but that's not what I want to do because I want to use an UPDATE JOIN rather than a subquery.

Answer 1

One method is pre-aggregation:

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;