DECLARE @MITagging Table 
(	ReqID int not null, 
	CustomerName char(6) not null, 
	MITaggingStatus varchar(9) not null,
	AssignedTo varchar(5) null)

INSERT INTO @MITagging
SELECT 1 ,'Cust 1', 'Done', NULL UNION
SELECT 2 ,'Cust 2', 'Pending', NULL UNION
SELECT 3 ,'Cust 3', 'Pending', NULL UNION
SELECT 4 ,'Cust 4', 'Done', NULL UNION 
SELECT 5 ,'Cust 5', 'Pending', NULL

DECLARE @UserRights Table ( UserID varchar(5) not null, CurrentStatus varchar(8) not null)

INSERT INTO @UserRights
SELECT 'User1', 'Active' UNION
SELECT 'User2', 'Active' UNION
SELECT 'User3', 'InActive'

SELECT UserID, UserRow = ROW_NUMBER() OVER( PARTITION BY CurrentStatus ORDER BY CurrentStatus)
      FROM @UserRights
	  Where CurrentStatus = 'Active'

SELECT REQID, ReqRow = ROW_NUMBER()  OVER (PARTITION BY MITaggingStatus  ORDER BY MITaggingstatus)
      FROM @MITagging
	  Where MITaggingStatus = 'Pending'
------------------------------------------
;WITH cteActiveUsers AS 
	( SELECT UserID, UserRow = ROW_NUMBER() OVER( PARTITION BY CurrentStatus ORDER BY CurrentStatus)
      FROM @UserRights
	  Where CurrentStatus = 'Active' )
 

 , ctePending AS 
	( SELECT REQID, ReqRow = ROW_NUMBER()   OVER (PARTITION BY MITaggingStatus  ORDER BY MITaggingstatus)
      FROM @MITagging
	  Where MITaggingStatus = 'Pending' )

UPDATE t
SET AssignedTo = cteActiveUsers.UserID
    ,MITaggingStatus = 'Done'
FROM cteActiveUsers 
		join ctePending on UserRow = ReqRow
		join @MITagging t on t.ReqID = ctePending.REQID
WHERE MITaggingStatus = 'Pending'

SELECT * FROM @MITagging