none
Updating table with the row number RRS feed

  • Question

  • Hi,

    I'm searching the manner to update an already populated SQL Server 2005 table with the row number.

    Any helps to me, please? Many thanks

    Tuesday, April 5, 2011 2:57 PM

Answers

  • You can use ROW_NUMBER function, and enumerate the rows based on a partition and order logic.

    Example:

    with rs as (
    select customerid, orderdate, orderid, rownumber, row_number() over(partition by customerid order by orderdate, orderid) as rn
    from dbo.orders
    )
    update rs
    set rownumber = rn;
    GO

    Check BOL for more info about ranking functions and common table expressions. Keep in mind that this statement is enumerating curretn rows, and will not mantain the enumaration automatically for new rows and neither mantain the enumeration out of gaps when you delete a row.

     


    AMB

    Some guidelines for posting questions...

    Tuesday, April 5, 2011 3:03 PM
    Moderator
  • WITH CTE AS 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY IDCol) Rn
    FROM TABLE_Name)
    
    UPDATE t
    SET t.RowNumber = tbl.Rn
    FROM TABLE_NAME t JOIN CTE tbl 
    ON t.IDCol = tbl.IDCol
    WHERE t.RowNumber IS NULL
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, April 5, 2011 3:03 PM
  • Can you please elaborate on your question?

    In the simplest term, the solution may be:

     

    ;with cte as (select PK, Row, row_Number() over (ORDER BY FieldWeNeedToOrder) as NewRow from TableToUpdate)
    
    UPDATE cte SET Row = NewRow
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 5, 2011 3:04 PM
    Moderator
  • Can you make an effort and try to write the statement based on the example we posted?

    The best way to learn something new is getting dirty.

    with rs as (
    select customercode, customerdescription, row_number() over(order by customerdescription) as rn
    from T
    )
    update rs
    set customercode = rn;
    GO

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by pscorca Tuesday, April 5, 2011 3:57 PM
    Tuesday, April 5, 2011 3:30 PM
    Moderator

All replies

  • You can use ROW_NUMBER function, and enumerate the rows based on a partition and order logic.

    Example:

    with rs as (
    select customerid, orderdate, orderid, rownumber, row_number() over(partition by customerid order by orderdate, orderid) as rn
    from dbo.orders
    )
    update rs
    set rownumber = rn;
    GO

    Check BOL for more info about ranking functions and common table expressions. Keep in mind that this statement is enumerating curretn rows, and will not mantain the enumaration automatically for new rows and neither mantain the enumeration out of gaps when you delete a row.

     


    AMB

    Some guidelines for posting questions...

    Tuesday, April 5, 2011 3:03 PM
    Moderator
  • WITH CTE AS 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY IDCol) Rn
    FROM TABLE_Name)
    
    UPDATE t
    SET t.RowNumber = tbl.Rn
    FROM TABLE_NAME t JOIN CTE tbl 
    ON t.IDCol = tbl.IDCol
    WHERE t.RowNumber IS NULL
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, April 5, 2011 3:03 PM
  • Can you please elaborate on your question?

    In the simplest term, the solution may be:

     

    ;with cte as (select PK, Row, row_Number() over (ORDER BY FieldWeNeedToOrder) as NewRow from TableToUpdate)
    
    UPDATE cte SET Row = NewRow
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 5, 2011 3:04 PM
    Moderator
  • Hi,

    I have a simple Customers table, with CustomerCode and CustomerDescription. I have value only for CustomerDescription and I want to feed the CustomerCode with the row_number() value. I haven't any duplicate values for CustomerDescription.

    Thanks

    Tuesday, April 5, 2011 3:23 PM
  • Can you make an effort and try to write the statement based on the example we posted?

    The best way to learn something new is getting dirty.

    with rs as (
    select customercode, customerdescription, row_number() over(order by customerdescription) as rn
    from T
    )
    update rs
    set customercode = rn;
    GO

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by pscorca Tuesday, April 5, 2011 3:57 PM
    Tuesday, April 5, 2011 3:30 PM
    Moderator
  • The best way for your situation may be to make a new table with the identity field, 

     

    insert into NewTable (CustomerDescription)
    
    select CustomerDescription from Customers
    

     

    Now delete / rename the original Customers and rename the new table as Customers. Each new customer will get a new code automatically.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, April 5, 2011 3:34 PM
    Moderator