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

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  
  7.   
  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. )  
  6.   
  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.