SQL Server Developer Center > SQL Server Forums > Transact-SQL > UPDATE TABLE using ROW_NUMBER() OVER...
Ask a questionAsk a question
 

AnswerUPDATE TABLE using ROW_NUMBER() OVER...

  • Wednesday, July 05, 2006 1:09 PMKur Lan Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     

    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

     

Answers

  • Wednesday, July 05, 2006 6:46 PMUmachandar Jayachandran - MS Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    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

All Replies

  • Wednesday, July 05, 2006 2:06 PMJens K. Suessmeyer -MSFT, ModeratorUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    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 PMKur Lan Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     

    Thank you!

     

  • Wednesday, July 05, 2006 6:46 PMUmachandar Jayachandran - MS Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    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