Hi friends, I was working on a scenario in which I need to assign a unique values to a column. I was trying to update the column using window ranking function ROW_NUMBER() but I got error that ‘Windowed function can only appear in SELECT or ORDER BY clause’.
Then I do some workaround and used the windows function indirectly using a CTE (Common Table Expression) for this. I will show you steps by steps how I accomplished this using CTE.
Let’s first create a table with some test data:
CREATE TABLE Test
Value VARCHAR(10) NOT NULL
INSERT INTO Test (Value) VALUES(‘Sunday’),(‘Monday’),(‘Tuesday’),(‘Wednesday’),(‘Thursday’),(‘Friday’),(‘Saturday’)
As we can see that in column ID NULL values gets 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:
SET ID = ROW_NUMBER() OVER(ORDER BY Value)
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
SET ID = RN
FROM CTE C JOIN Test T ON T.value = C.Value
When I run this sql code I didn’t get any error and ID column values get updated with unique values.
That’s all folks. Hope you like this post.