Update int column in a table with unique incrementing values

A practical walkthrough

Let's tackle this with an efficient SQL variable to assign incrementing values. This approach is particularly useful when you need a custom start point or where null InterfaceID values are present:

-- pretending to be a SQL genie
SET @newId := (SELECT MAX(`InterfaceID`) FROM `YourTable`);
-- Poof! Your wish is my command!
UPDATE `YourTable` SET `InterfaceID` = (@newId := @newId + 1) WHERE `InterfaceID` IS NULL;


Beware: to avoid mortal sin of creating ID collisions, ensure @newId is set above the maximum existing value.

Advanced updating techniques

The lost and found method

When concerned about gaps in your ID sequence, use ROW_NUMBER() with a calculated offset:

WITH NumberedRows AS (
  SELECT `InterfaceID`, 
      ROW_NUMBER() OVER (ORDER BY `OrderColumn`) + (SELECT MAX(`InterfaceID`) FROM `YourTable`) AS NewID
  FROM `YourTable`
  WHERE `InterfaceID` IS NULL
)
UPDATE `YourTable` SET `InterfaceID` = NumberedRows.NewID
FROM NumberedRows
WHERE `YourTable`.`PrimaryKey` = NumberedRows.PrimaryKey;


This lost and found method ensures every newly assigned InterfaceID continues from the maximum existing ID value.

Self-join: the SQL doppelgänger

A self-join can be a powerful tool when it comes to updating rows based on their relative position in the dataset:

UPDATE t1
SET t1.`InterfaceID` = t2.RowNum
FROM `YourTable` t1
JOIN (
  SELECT `PrimaryKey`,
    ROW_NUMBER() OVER (ORDER BY OrderColumn) AS RowNum
  FROM `YourTable` 
  WHERE `InterfaceID` IS NULL
) t2
ON t1.`PrimaryKey` = t2.PrimaryKey;


When it comes to Postgres, well, it just likes attention and uses its own SERIAL or SEQUENCE for auto-incrementing fields.

Navigating tricky updates

Custom increments & conditions

When dealing with non-linear increments or bespoke conditions, exercise your SQL skills with a CASE statement inside your update query:

SET @rank:=0;
UPDATE `YourTable` 
SET `InterfaceID` = CASE
  -- Look mum, I'm an SQL superhero handling custom conditions!
  WHEN Condition THEN @rank:= @rank + CustomIncrement
  -- Regular Joe increment? No worries, here I'm default Clark Kent! 
  ELSE @rank:= @rank + 1
END
WHERE `InterfaceID` IS NULL;


Replace Condition with your conditions and CustomIncrement with your preferred step for increments.

Be mindful of primary key constraints

Respect the primary key, the royalty of columns. Ensure that your updates do not introduce duplicate values in this column.

The maverick ORDER BY

Just because you're stuck at home doesn't mean your SQL queries should be. The ORDER BY clause in ROW_NUMBER() allows you to frolic around based on different data orderings.