Clever Geek Handbook

Increasing int column in MS SQL Server table via SP

Is there a T-SQL statement to automatically populate an empty column in a table with incremental values ​​starting at one specific value?

eg.

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

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

Any help is appreciated!

+2


source to share


2 answers 


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


+3


source


It works. You need to separate the ranking function 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
+2


source






More articles:


All Articles