I have a HRUser
and an Audit
table, both are in production with large number of rows.
Now I have added one more column to my HRUser
table called IsActivated
.
I need to create a one-time script which will be executed in production and populate data into this IsActivated
column. After execution of this one-time script onwards, whenever the users activate their account, the HRUser
table's IsActivated
column will automatically be updated.
For updating the IsActivated
column in the HRUser
table, I need to check the Audit
table whether the user has logged in till now.
UPDATE [dbo].HRUser
SET IsActivated = 1
FROM dbo.[UserAudit] A
JOIN dbo.[HRUser] U ON A.UserId = U.UserId
WHERE A.AuditTypeId = 14
AuditTypeId=14
means the user has logged in and user can login any number of times and every time the user logs in it will get captured in the UserAudit
table...
The logic is that if the user has logged in at least once means the user is activated.
This cannot be tested on lower environments and it need to be directly executed on production as in lower environments we don’t have any data in the UserAudit
table.
I am not really sure if that works as I have never used joins in update statement, I am looking for suggestions for any better approach than this for accomplishing my task
You could use EXISTS
and a correlated subquery to filter on rows whose UserId
has at least one audit event of id 14
:
UPDATE h
SET IsActivated = 1
FROM [dbo].HRUser h
WHERE EXISTS (
SELECT 1 FROM
FROM dbo.[UserAudit] a
WHERE a.UserId = h.UserId AND a.AuditTypeId = 14
)
Note that there is no point reopening the target table in the subquery; you just need to correlate it with the outer query.