Posted in 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

Author:

I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on http://www.sqlservercentral.com/blogs/kapil-blogs/

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s