How To Set Column Value Equal To Row No?

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

Solution 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]
INNERJOIN
    (
        SELECT
            [test_id],
            ROW_NUMBER() OVER (ORDERBY [test_id]) AS rn
        FROM
            [test123]
    ) AS x
ON 
    [test123].[test_id] = [x].[test_id]

Solution 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 (orderby ID) asvarchar) +' Where ID = '+cast(ID asvarchar) 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.