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?

score 124 · Accepted Answer · edited Jan 11 '21 at 12:55

124

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

edited Jan 11 '21 at 12:55

Syed Farjad Zia Zaidi

3,302
4
27
50

answered Nov 29 '12 at 15:57

WKordos

2,167
1
16
15

2

check [this](http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx/) too, hope helps someone. – Shaiju T Jan 28 '16 at 10:54
7

Add parentheses around `(SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)` – Pat Migliaccio Jul 05 '16 at 15:22
1

i was using a CURSOR to perform some task which took 15 minutes to process 80000 records. i just used this way and now that task take only 4 seconds. saved my life. thanks. – sithumc Aug 17 '17 at 03:19
How come you have +1 on both the `SELECT` and the `SET`? Shouldn't it just be on one of them? – DNKROZ Sep 04 '17 at 11:23
Man I was doing a while loop with @i+1 then an update statement until there were none left. I let this run over night and it had done about 600,000 records ran this and it was done in 1 minute 40 seconds to do 700,000 records – Anglefroghammer Jul 16 '19 at 08:21
this solution updates 8 records by 8 records, i mean each time new set is done for 8 data, then @i =@i +1 processed, i dont know why but does not work for me – Azzurro94 Nov 11 '20 at 17:18
`ORA-06550: line 1, column 9: PLS-00103: Encountered the symbol "@" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210100", line 673 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210100", line 659 ORA-06512: at "APEX_210100.WWV_FLOW_DYNAMIC_EXEC", line 1855` – alhelal Jun 30 '21 at 06:35

score 23 · Answer 2 · edited Mar 04 '16 at 00:18

23

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

edited Mar 04 '16 at 00:18

Alex

21,273
10
61
73

answered Mar 03 '16 at 10:31

ram nainar

231
2
2

Developer · Answer 3 · 2015-01-14T12:40:05.143

12

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;

edited Jan 14 '15 at 12:40

answered Jan 14 '15 at 12:26

Developer

3,857
4
37
47

Kevin Swann · Answer 4 · 2017-11-15T09:53:43.973

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.

score 4 · Answer 5 · edited Jan 26 '17 at 09:58

4

For Postgres

ALTER TABLE table_name ADD field_name serial PRIMARY KEY

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

edited Jan 26 '17 at 09:58

Moinuddin Quadri

46,825
13
96
126

answered Jan 26 '17 at 09:55

Nikhil Bhardwaj

41
2

score 2 · Answer 6 · answered Nov 22 '15 at 08:32

2

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

update table set interfaceID=RowNum where condition;

answered Nov 22 '15 at 08:32

HamedKhan

61
1
1
6

score 1 · Answer 7 · answered Nov 29 '12 at 16:00

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

score 0 · Answer 8 · edited May 03 '22 at 02:04

0

My table has 500 million records. The below code worked for me.

-- update rows using a CTE - Ervin Steckl 

;WITH a AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn, id
FROM accounts2
) 
UPDATE a SET id=rn
OPTION (MAXDOP 1)

https://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/

edited May 03 '22 at 02:04

karel

5,489
46
45
50

answered May 02 '22 at 19:26

Raja

1

score -1 · Answer 9 · edited Feb 27 '20 at 11:36

-1

You can try :

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

edited Feb 27 '20 at 11:36

ekans

1,662
14
25

answered Feb 27 '20 at 09:57

Izzy

1
2

Update int column in table with unique incrementing values

9 Answers9

Linked