CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Robert Keith
Robert Keith

Reputation: 104

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

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.

Upvotes: 0

Views: 358

Answers (2)

Madhukar
Madhukar

Reputation: 1242

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)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 0

Related Questions