none
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

     

    Wednesday, July 05, 2006 1:09 PM

Answers

  • 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
    Wednesday, July 05, 2006 6:46 PM

All replies

  • 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
    ---

    Wednesday, July 05, 2006 2:06 PM
  • Thank you!

     

    Wednesday, July 05, 2006 2:09 PM
  • 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
    Wednesday, July 05, 2006 6:46 PM
  • 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
    Wednesday, January 06, 2010 1:06 PM
  • 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
    Wednesday, January 06, 2010 2:24 PM
  • Great, thanks!
    Monday, May 17, 2010 9:37 AM
  • *Thank you*, it worked for me.
    Wednesday, February 16, 2011 10:28 PM
  • Thanks yet again! Just what I needed, just when I was looking forward to it!

     

    Thanks,

    Bill in Massachusetts, USA

    Wednesday, March 30, 2011 7:04 PM
  • Can it be used in oracle?
    Friday, August 15, 2014 3:02 AM