How do I Loop through a table and update a field in SQL, Sql Server

Language: Layout:

create table Renewals (
        [RowId]   int     not null
      , AccountId char(1) not null
      , OrderId   int         null
    );
    insert into Renewals (RowId, AccountId) values
      (1,'A'), (2,'A'), (4,'A')
    , (5,'B'), (6,'B'), (7,'C');

with cte as (
      select 
           [RowId]
        ,  AccountId
        ,  OrderId
        ,  NewOrderId = row_number() over (
              partition by AccountId
              order by [RowId]
        )
      from Renewals
    )

update cte
      set OrderId = NewOrderId;
    /* ------------------------------ */ 
    select * from Renewals
    /* ------------------------------ */ 
    select 
        r.*
      , RenewedBy=p.RowId 
    from Renewals as r 
      left join Renewals as p 
        on p.AccountId = r.AccountId 
       and p.OrderId = r.OrderId+1
    /* ------------------------------ */        
    ;with cte as (
      select *
        ,  rn = row_number() over (
              partition by AccountId
              order by [RowId]
        )
      from Renewals
    )
    select 
        r.RowId
      , r.AccountId
      , OrderId = r.rn
      , RenewedBy=p.RowId 
    from cte as r 
      left join cte as p 
        on p.AccountId = r.AccountId 
       and p.rn = r.rn+1

View schema
Execution time: 0,03 sec, rows selected: 18, rows affected: 12, absolute service time: 0,19 sec