Update int column in table with unique incrementing values

sql, sql-server, sql-server-2008

Solution

declare @i int  = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)


update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

should do the work

Problem

I am trying to populate any rows missing a value in their `InterfaceID (INT)` column with a unique value per row. I'm trying to do this query: ``` UPDATE prices SET interfaceID = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) WHERE interfaceID IS null ``` I was hoping the the `(SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)` would be evaluated for every row, but its only done once and so all my affected rows are getting the same value instead of different values. Can this be done in a single query?

Original source