2

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.

sam
  • 1,242
  • 3
  • 12
  • 31

1 Answers1

9

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
dario
  • 5,149
  • 12
  • 28
  • 32
  • @sam you mean that `name` is `NULL`? – dario Jan 08 '15 at 09:40
  • my apologies ... the reason for the bug is because name column has duplicate entries and with inner join total records with become more. For ex if there are 3 records and out of these 3 one is duplicate then inner join will give 4 records and not 3 – sam Jan 08 '15 at 09:51
  • @sam that's why you should use a primary key. – dario Jan 08 '15 at 09:55
  • I understand that the table is poorly designed. I have to delete the duplicate entries from the table and for this i need a unique coloumn added in the table. now what should i do.. i can not drop the table – sam Jan 08 '15 at 10:19
  • @sam I don't understand you. Anyway, was this answer usefull? – dario Jan 08 '15 at 10:31
  • I have edited my question with a example table. any help is deeply appriciated – sam Jan 08 '15 at 11:32
  • @sam I think this is beyond the scope of the question. You asked how to update your table, and you have it. Now this is another problem. – dario Jan 08 '15 at 11:45
  • Anyways thanks for your help and time ..i am myself very confused..no issues – sam Jan 08 '15 at 11:49
  • @sam Please, have a look at [this](http://stackoverflow.com/help/someone-answers). And for remove duplicate rows at [this](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) – dario Jan 08 '15 at 11:53