Development

SQL Server – Using Window ranking function with UPDATE statement

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

Rank_1

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

(

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

UPDATE Test

SET ID = ROW_NUMBER() OVER(ORDER BY Value)

GO

Rank_2

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 run this sql code I didn’t get any error and ID column values get updated with unique values.

Rank_3

That’s all folks. Hope you like this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s