UPDATE TABLE using ROW_NUMBER() OVER...

Answered UPDATE TABLE using ROW_NUMBER() OVER...

  • Wednesday, July 05, 2006 1:09 PM
     
     

    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

     

All Replies

  • Wednesday, July 05, 2006 2:06 PM
    Moderator
     
     
    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:09 PM
     
     

    Thank you!

     

  • Wednesday, July 05, 2006 6:46 PM
     
     Answered
    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, January 06, 2010 1:06 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 2:24 PM
    Moderator
     
      Has Code

    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
  • Monday, May 17, 2010 9:37 AM
     
     
    Great, thanks!
  • Wednesday, February 16, 2011 10:28 PM
     
     
    *Thank you*, it worked for me.
  • Wednesday, March 30, 2011 7:04 PM
     
     

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

     

    Thanks,

    Bill in Massachusetts, USA