Update int column in table with unique incrementing values

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For Postgres  ALTER TABLE table name ADD field name serial PRIMARY KEY   REFERENCE  https   www tutorialspoint com postgresql postgresql using autoincrement htm

User · Answer

DECLARE  IncrementValue int SET  IncrementValue   0  UPDATE Samples SET qty    IncrementValue  IncrementValue  IncrementValue 1

User · Answer

You can try     DECLARE  counter int SET  counter   0 UPDATE  table  SET  column     counter   counter    counter   1

User · Answer

In oracle-based products you may use the following statement   update table set interfaceID RowNum where condition

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008