SQL server ROW_NUMBER() OVER(PARTITION issue

Question

I have a table named SavingsMaster. Below is a sample output.

+--------+------------------+-------+
| CustNo |    AccountNo     | Cycle |
+--------+------------------+-------+
|      1 | 48SVAS1521144100 |       |
|      2 | 21SVSV1300854500 |       |
|      2 | 21SVAS1308476900 |       |
|      1 | 48SVAS1411737700 |       |
+--------+------------------+-------+

As you can see, both customers have unique account no. My requirement is to update the Cycle column group by CustNo, AccountNo.

I can use the below select query but I'm unable to use it in an update clause.

 SELECT CustNo, AccountNo,
        ROW_NUMBER() OVER(PARTITION BY CustNo  ORDER BY AccountNo)    as RowNumber 
 FROM SavingsMaster

Seek your help to write an update statement.

Many thanks...

Answer 1

Try this way:

UPDATE SavingsMaster
SET Cycle=T1.RowNumber
FROM
     (SELECT CustNo, AccountNo, ROW_NUMBER() OVER(PARTITION BY CustNo  ORDER BY AccountNo) as 'RowNumber'  
      FROM SavingsMaster) as T1 JOIN
SavingsMaster S ON S.CustNo=T1.CustNo AND S.AccountNo=T1.AccountNo

Answer 2

If you want a sequential number, then you can do this using an updatable CTE:

WITH toupdate AS (
      SELECT CustNo, AccountNo,
             ROW_NUMBER() OVER (PARTITION BY CustNo ORDER BY AccountNo) as RowNumber 
      FROM SavingsMaster
     )
UPDATE toupdate
    SET cycle = RowNumber;