how to set column value equal to row no?

Question

How can i set value of column that has been added after altering table equal to row no in sql server 2008. That is i want value of the column equal to no. of row. I also want this field to allow NULL values. So it is like auto increment but allowing null values that's why don't want to use identity or primary key column with auto increment. So how can it be set to row no? Any help will be appreciated.

Answer 1

If you try to UPDATE a column directly using ROW_NUMBER() you'll get...

Windowed functions can only appear in the SELECT or ORDER BY clauses.

...so instead INNER JOIN the table to itself...

UPDATE
    [test123]
SET
    [row_number] = [x].[rn]
FROM
    [test123]
INNER JOIN
    (
        SELECT
            [test_id],
            ROW_NUMBER() OVER (ORDER BY [test_id]) AS rn
        FROM
            [test123]
    ) AS x
ON 
    [test123].[test_id] = [x].[test_id]

Answer 2

what about this : It will update the value of the desired column to the row number.

select  'Update myTable set row_No = ' + 
CAST(ROW_NUMBER() over (order by ID) as varchar) + ' Where ID = ' +  
cast(ID as varchar) from myTable

--The result will produce an update statement which when you run it will update the value of the desired column to the row number.