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

Answer 4

Hi,

i have this table 

ID	EXP	Nation
11	2456	1
22	123	1
33	12556	1
44	23	2

and i need a query to ORDER me the table by EXP and to update the 1st ROW  Nation = '1'

the  2'nd row mut be Nation = '2'

the 3'rd row  Nation ='1'

the 4'th row Nation = '2'

and so on

I have 8000 rows and to edit it manualy take some time, i search for this problem 2 days but i dont finde a solution so i hope you can save some days from my life :))

Thx.

The resultat must be so

ID	EXP	Nation
33	12556	1
11	2456	2
22	123	1
44	23	2

Answer 5

From the top of my head - may need tweaking:

;with cte as (select ID, Exp, Nation, row_number() over (order by Exp Desc) as RowNum from myTable)

update cte set Nation = case RowNum % 2 = 1 then 1 else 2 end

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 6

Great, thanks!

Answer 7

*Thank you*, it worked for me.

Answer 8

Thanks yet again! Just what I needed, just when I was looking forward to it!

 

Thanks,

Bill in Massachusetts, USA