Incrementing int column in MS SQL Server table via SP

Question

Is there a T-SQL statement to auto fill an empty column in a table with incremented values starting at one specific value?

E.g.

UPDATE A SET A.NO = ROW_NUMBER() OVER (ORDER BY A.NO) + @max WHERE A.NO is NULL

This statement doen't work and I don't know how to make it run...

Any help is appreciated!

Answer 1

This works. You need to decouple the ranking fucntion from the update

UPDATE
    bar
SET
    NO = bar.foo + @max
FROM
    (SELECT
         A.NO,
         ROW_NUMBER() OVER (ORDER BY A.NO) AS foo
     FROM
         A
     WHERE
         A.NO is NULL
     ) bar

Answer 2

WITH    q AS
        (
        SELECT  a.*, MAX(no) OVER() + ROW_NUMBER() OVER (ORDER BY a.no) AS rn
        FROM    a
        )
UPDATE  q
SET     no = rn