In a stored procedure how can you loop table A and then update table B

Question

Within a stored procedure I need to loop the following table:

SELECT *
FROM dbo.UsersInRoles
INNER JOIN Application_Users ON UsersInRoles.AppUserID = Application_Users.AppUserID
WHERE (UsersInRoles.ApplicationId = @ApplicationId)
AND (UsersInRoles.RoleId = @CurrentRoleId)
AND (Application_Users.LastLogin < @StartDate)

And for each record that is looped I need to perform this update:

UPDATE UsersInRoles
SET UsersInRoles.RoleId = @DenyRoleId
WHERE (UsersInRoles.ApplicationId = @ApplicationId)
AND (UsersInRoles.RoleId = @CurrentRoleId)

If there is a better way to perform this then I'm open to suggestions. Basically the 1st query does a filter based upon the INNER JOIN to determine which records need to be updated. Then those filtered records are looped and updated with a new RoleID.

answered in https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server — nico boey, Oct 10 '17 at 13:13
You need to make a paradigm shift in the way you look at data. Looping is horribly inefficient. You have to change your mindset from thinking about what you need to do to a row and instead think about what you need to do to a column. — Sean Lange, Oct 10 '17 at 13:16

score 1 · Accepted Answer · answered Oct 10 '17 at 13:18

Update with join:

UPDATE UIR
SET UIR.RoleId = @DenyRoleId
FROM UsersInRoles UIR
INNER JOIN Application_Users ON UIR.AppUserID = Application_Users.AppUserID
WHERE (UIR.ApplicationId = @ApplicationId)
AND (UIR.RoleId = @CurrentRoleId)
AND (Application_Users.LastLogin < @StartDate)

score 0 · Answer 2 · answered Oct 10 '17 at 13:15

You can use a CTE to do the UPDATE:

;WITH ToUpdate AS (
   SELECT uir.RoleId 
   FROM dbo.UsersInRoles AS uir
   INNER JOIN Application_Users AS au 
      ON uir.AppUserID = au.AppUserID 
   WHERE (uir.ApplicationId = @ApplicationId) AND 
         (uir.RoleId = @CurrentRoleId) AND 
         (au.LastLogin < @StartDate)
)
UPDATE ToUpdate
SET RoleId = @DenyRoleId

So, you just type wrap the query in a CTE and then perform the UPDATE on the table returned by the CTE. The RoleId value will be propagated to the records of the actual table, UsersInRoles.

In a stored procedure how can you loop table A and then update table B

2 Answers2