Update sequential number column over another column values

This is something I had to do recently. If you don't get this right this can be really complex.

Lets say you have a table named Invoice which has data as in the below screenshot. So you want to create a new ID column which will be auto incremented for each Customer. i.e CustomerID 1 will have 1,2,3,4 values and then for CustomerID 2 it will again start from 1..

You can do this using a after update trigger. But in order to populate column for existing data what would you do. Most of you might say use a Cursor, but more often than not there is a SET based query we can use to do things Cursors do. So here also you can use ROW_NUMBER() OVER to accomplish this task.

Lets create the table for this example and lets keep it simple;

CREATE TABLE dbo.Invoice
(
InvoiceID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT,
Amount MONEY,
PAID BIT DEFAULT 0
)

Now lets insert some test data;

INSERT INTO dbo.Invoice VALUES 
(1,200,0),
(1,250,0),
(1,400,0),
(1,320,0),
(2,120,0),
(2,50,0),
(3,10,0),
(4,150,0),
(4,100,0),
(4,140,0)

Now lets add the new column to be populated;

ALTER TABLE dbo.Invoice 
ADD CustomerInvoiceNumber INT

Now is the real query which populates the column based on the CustomerID;

;WITH CTE (InvoiceID,CustomerInvoiceNumber) AS 
(
SELECT InvoiceID,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InvoiceID) FROM dbo.Invoice 
)

UPDATE I 
SET CustomerInvoiceNumber = CTE.CustomerInvoiceNumber
FROM 
dbo.Invoice I INNER JOIN CTE ON I.InvoiceID = CTE.InvoiceID

We are using a CTE because we can not use PARTITION BY in the Update clause directly. This will update the CustomerInvoiceNumber Column with the desired values as below;