AFI workflow member sequence change

 USER_ID Approver_Sequence NewSequence

mlfoster 2 1

ismirnova4 2

hnandha 6 3

tpenny 7 4

cmignat 8 5

ytan      9      6

blerner 10 7

cheuschmid 11 8

tvadaketh 12 9

ngrasberger 13 10

using rownumber will update it 
cte first 

SELECT USER_ID, Approver_Sequence, 

           ROW_NUMBER() OVER (ORDER BY Approver_Sequence ) AS NewSequence

    FROM tbl_WorkFlowMembers

where WorkFlow_ID='RAILAUBre00001'

WITH OrderedApprovers AS (

SELECT USER_ID, Approver_Sequence, 

           ROW_NUMBER() OVER (ORDER BY Approver_Sequence ) AS NewSequence

    FROM tbl_WorkFlowMembers

where WorkFlow_ID='RAILAUBre00001'

)

UPDATE tbl_WorkFlowMembers

SET Approver_Sequence = OrderedApprovers.NewSequence

FROM tbl_WorkFlowMembers

JOIN OrderedApprovers ON tbl_WorkFlowMembers.User_ID = OrderedApprovers.User_ID

where  WorkFlow_ID='RAILAUBre00001'