UPDATE TABLE using ROW_NUMBER() OVER...

Question

Hi Champs,

I am trying to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table

table:
ID------Col2----Col3---
1-------12---------1
1-------34---------2
2-------44---------1
2-------75---------2
2-------77---------3
3-------23---------1
3-------33---------2
4-------44---------1
4-------22---------2

I know I can get Col3 right with an SELECT and ROWNUMBER() OVER, but how can I UPDATE  the table with the result?

 

 

/Many thanks

 

Answer 1

Hi,

here is a sample:

DROP TABLE SomeTable

GO

CREATE TABLE SOmeTable

(

IDCol INT,

ValueCol INT,

RANKCol INT

)

INSERT INTO SomeTable VALUES(1,1,NULL)

INSERT INTO SomeTable VALUES(1,2,NULL)

INSERT INTO SomeTable VALUES(2,3,NULL)

INSERT INTO SomeTable VALUES(2,4,NULL)

INSERT INTO SomeTable VALUES(3,5,NULL)

INSERT INTO SomeTable VALUES(3,6,NULL)

INSERT INTO SomeTable VALUES(4,7,NULL)

INSERT INTO SomeTable VALUES(4,8,NULL)

UPDATE SomeTable

SET RankCol = SubQuery.Rank

FROM

(

SELECT IDCol, ValueCol, DENSE_RANK() OVER (PARTITION BY IDCOL ORDER BY ValueCOL) AS Rank

FROM SomeTable

) SubQuery

INNER JOIN SomeTable ON

SubQuery.IDCol = SomeTable.IDCol

AND SubQuery.ValueCol = SomeTable.ValueCol

SELECT * From SomeTable

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

Thank you!

 

Answer 3

You can use a CTE in any DML statement. So it is simpler to just do:

 

with r_SomeTable
as
(
select *

, row_number() over(partition by IDCol order by ValueCol) as rnk
from SomeTable
)
update r_SomeTable
set RANKCol = rnk
go