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

I need to update a column with an sequential number starting with 1 based on the accountId in the table. How do I do this?

so OrderID is NULL to start.
so for each row of AccountID I need to update the OrderID to start from 1 and sequentially update so my data comes out like this.
The table is called Renewals

RowID  AccountID  OrderID
1      A          1
2      A          2
4      A          3
5      B          1
6      B          2
7      C          1

Using a common table expression with row_number() to partition by AccountId and order by [RowId]:

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

Without using the common table expression:

update r
  set OrderId = NewOrderId
  from (
    select *
      ,  NewOrderId = row_number() over (
            partition by AccountId
            order by [RowId]
      )
    from Renewals
      ) as r

test setup: http://rextester.com/FSUD49402

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;

results in:

+-------+-----------+---------+
| RowId | AccountId | OrderId |
+-------+-----------+---------+
|     1 | A         |       1 |
|     2 | A         |       2 |
|     4 | A         |       3 |
|     5 | B         |       1 |
|     6 | B         |       2 |
|     7 | C         |       1 |
+-------+-----------+---------+

To answer the question in the comment of this answer:


I am using this data to update the previous row. [...] I am trying to show that Row 1 above was renewed by row 2 was replaced by row 4 and row4 has not been renewed as yet

Can be done with:

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

returns:

+-------+-----------+---------+-----------+
| RowId | AccountId | OrderId | RenewedBy |
+-------+-----------+---------+-----------+
|     1 | A         |       1 | 2         |
|     2 | A         |       2 | 4         |
|     4 | A         |       3 | NULL      |
|     5 | B         |       1 | 6         |
|     6 | B         |       2 | NULL      |
|     7 | C         |       1 | NULL      |
+-------+-----------+---------+-----------+

If this is the only reason for the previous update, we don't have to update the table at all. This will get the same results as above:

;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

StackExchange Database Administrators Q#164781, answer score: 8