[sql] Update int column in table with unique incrementing values

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?

simple query would be, just set a variable to some number you want. then update the column you need by incrementing 1 from that number. for all the rows it'll update each row id by incrementing 1

SET @a  = 50000835 ;  
UPDATE `civicrm_contact` SET external_identifier = @a:=@a+1 
WHERE external_identifier IS NULL;

Assuming that you have a primary key for this table (you should have), as well as using a CTE or a WITH, it is also possible to use an update with a self-join to the same table:

UPDATE a
SET a.interfaceId = b.sequence
FROM prices a
INNER JOIN
(
   SELECT ROW_NUMBER() OVER ( ORDER BY b.priceId ) + ( SELECT MAX( interfaceId ) + 1 FROM prices ) AS sequence, b.priceId
   FROM prices b
   WHERE b.interfaceId IS NULL
) b ON b.priceId = a.priceId

I have assumed that the primary key is price-id.

The derived table, alias b, is used to generated the sequence via the ROW_NUMBER() function together with the primary key column(s). For each row where the column interface-id is NULL, this will generate a row with a unique sequence value together with the primary key value.

It is possible to order the sequence in some other order rather than the primary key.

The sequence is offset by the current MAX interface-id + 1 via a sub-query. The MAX() function ignores NULL values.

The WHERE clause limits the update to those rows that are NULL.

The derived table is then joined to the same table, alias a, joining on the primary key column(s) with the column to be updated set to the generated sequence.

You can try :

DECLARE @counter int
SET @counter = 0
UPDATE [table]
SET [column] = @counter, @counter = @counter + 1```

Try something like this:

with toupdate as (
    select p.*,
           (coalesce(max(interfaceid) over (), 0) +
            row_number() over (order by (select NULL))
           ) as newInterfaceId
    from prices
   )
update p
    set interfaceId = newInterfaceId
    where interfaceId is NULL

This doesn't quite make them consecutive, but it does assign new higher ids. To make them consecutive, try this:

with toupdate as (
    select p.*,
           (coalesce(max(interfaceid) over (), 0) +
            row_number() over (partition by interfaceId order by (select NULL))
           ) as newInterfaceId
    from prices
   )
update p
    set interfaceId = newInterfaceId
    where interfaceId is NULL

In oracle-based products you may use the following statement:

update table set interfaceID=RowNum where condition;

DECLARE @IncrementValue int
SET @IncrementValue = 0 
UPDATE Samples SET qty = @IncrementValue,@IncrementValue=@IncrementValue+1

For Postgres

ALTER TABLE table_name ADD field_name serial PRIMARY KEY

REFERENCE: https://www.tutorialspoint.com/postgresql/postgresql_using_autoincrement.htm