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’:

SQL Server

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,

  1. CREATE TABLE Test
  2. (
  3. ID INT,
  4. Value VARCHAR(10) NOT NULL
  5. )
  6. GO
  8. INSERT INTO Test (Value) VALUES('Sunday'),('Monday'),('Tuesday'),('Wednesday'),('Thursday'),('Friday'),('Saturday')
  9. 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,

  1. UPDATE Test
  2. SET ID = ROW_NUMBER() OVER(ORDER BY Value)
  3. GO

SQL Server

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

  1. ;WITH CTE AS
  2. (
  3. SELECT Value, ROW_NUMBER() OVER(ORDER BY Value) as RN
  4. FROM Test
  5. )
  7. UPDATE T
  8. SET ID = RN
  9. 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.

SQL Server

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