Using Window Ranking Function With UPDATE Statement

Hi friends. I was working on a scenario in which I needed to assign a unique value to a column. I was trying to update the column using window ranking function ROW_NUMBER() but I got an error that ‘Windowed function can only appear in SELECT or ORDER BY clause’:

Then I did some workaround and used the Windows function indirectly using a CTE (Common Table Expression) for this. I will show you step by step how I accomplished this using CTE.

Let’s first create a table with some test data,

CREATE TABLE Test
(
    ID INT,
    Value VARCHAR(10) NOT NULL
)
GO

INSERT INTO Test (Value) VALUES('Sunday'),('Monday'),('Tuesday'),('Wednesday'),('Thursday'),('Friday'),('Saturday')
GO

As we can see that in column ID NULL values get inserted as we didn’t specify any values for this column during INSERT statement. So, when I tried this UPDATE statement using ROW_NUMBER() with SET I got the following error,

UPDATE Test
SET ID = ROW_NUMBER() OVER(ORDER BY Value)
GO

Then I used CTE to update the ID values in table test in this manner,

;WITH CTE AS
(
    SELECT Value, ROW_NUMBER() OVER(ORDER BY Value) as RN
    FROM Test
)

UPDATE T
SET ID = RN
FROM CTE C JOIN Test T ON T.value = C.Value

When I ran this SQL code I didn’t get any error and ID column values to get updated with unique values.

That’s all folks. I hope you like this post.