Update column with a dynamic sequence with Row

I tried to update in MSSQL a column(Y) of a table(A) with with an ascending sequence that resets itself when the value of another column(X) of the same table changes. Table A at the beginning:

id	X	Y
1	1	1
2	1	1
3	2	1
4	2	1
5	2	1
6	3	1

As it should be after the script:

id	X	Y
1	1	1
2	1	2
3	2	1
4	2	2
5	2	3
6	3	1

I tried with row_number() but in the loop, it modify all the rows : With a counter and variable to increment:

UPDATE dbo.A
SET "Y" = @MyInc
FROM (
            SELECT ROW_NUMBER() OVER ( "Id" ASC) AS row_num_Id
             , Id
             , X
             , Y
        FROM dbo.A)     AS sub
        WHERE row_num_Id = @MyCounter;

CodePudding user response：

This will give you the results you want

CREATE TABLE #T (
    Id INT NOT NULL, 
    X INT NOT NULL, 
    Y INT NOT NULL
)

INSERT INTO #T(Id, X, Y)
VALUES
(1, 1, 1),
(2, 1, 1),
(3, 2, 1),
(4, 2, 1),
(5, 2, 1),
(6, 3, 1);

GO

WITH WithRowNumbers AS (
    SELECT 
    Id, 
    X, 
    ROW_NUMBER() OVER (PARTITION BY X ORDER BY Id) As RowNumber
    FROM #T
)

UPDATE T 
SET Y = WRN.RowNumber
FROM WithRowNumbers AS WRN
INNER JOIN #T AS T ON T.Id = WRN.Id

SELECT * FROM #T

Or as @CharlieFace mentions you can simplify even more, as the CTE is like a view of the original table.

UPDATE T 
SET Y = T.RowNumber
FROM WithRowNumbers AS T;