CODEWITHSUNDEEP
sql-server-2008
sam
sam

Reputation: 1294

How to update a column with ROW_NUMBER() in SQL

I have added a new column to the table. Now i want to update this column with values from the function row_number().And i want to do it because i want to remove duplicate entries. The below mentioned code is not giving the desired output

UPDATE tab1
SET rownumber = (SELECT ROW_NUMBER() OVER(ORDER BY name ASC) 
             FROM tab1 AS a WHERE a.name = b.name)
FROM tab1 b

Problem is that when a new column is added in a table its values are null. Insert into adds new rows in the table but does not replace nulls. How to update null values with row numbers generated by function ROW_NUMBER().

The below mentioned table is what i have

name score

Bank of America 10

Commerce Bank 20

Wells Fargo Bank 135

Bank Midwest 45

Bank of America 10

Commerce Bank 20

now I want the output as after removing the duplicates

name score

Bank of America 10

Commerce Bank 20

Wells Fargo Bank 135

Bank Midwest 45

I was trying to add a new column with unique values to get the desired result.

Upvotes: 2

Views: 22265

Answers (1)

dario
dario

Reputation: 5259

Try this:

UPDATE T
SET T.rownumber = TT.ROW_ID
FROM tab1 AS T
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROW_ID
                  ,name
            FROM Tab1) AS TT
    ON T.name = TT.name

Upvotes: 9

Related Questions