How to update ID=null values in table to incremental counter values?

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows:

ID   NaturalID               Comment

1    franknfurther03071972   blahblah
2    chrisrock12081980       nonsense
null clairecampbell24121990  merry christmas
3    walterhermes22032001    young guy
4    tanjaolsen16051996      nice
null timharris20041999       came late

The rows with "null" IDs are new, the numbered IDs are those already existing in the main, target table. The NaturalID can uniquely identify an entry (in fact, it's multiple columns). I want to set the "null" IDs to incremental values, following the current max ID, here: 5 and 6, increasing when more null IDs are found.

Currently, I use a cursor to iterate over the rows with ID null and update each ID with a value, but sincce it's really a very big table, it would take days.

I tried to do an update with row_number(), but it gives me an error "Windowed functions can only appear in the SELECT or ORDER BY clauses.":

update StagingTable set ID=ROW_NUMBER() over (order by NaturalId)
from StagingTable where id is null  -- fails

How can I do it?

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + 
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.

StackExchange Database Administrators Q#69271, answer score: 6