CODEWITHSUNDEEP
sqloracle-database
W. Walter
W. Walter

Reputation: 349

Update numbers with a counter in SQL

Suppose I have a table containing increasing and irregularly incremented numbers (3, 4, 7, 11, 16,...) in the first column col1.

I want to update col1 such that the numbers will be 1000 plus the row number. So something like:

UPDATE tab1 SET col1 = 1000 + row_number()

I am using Oracle SQL and would appreciate any help! Many thanks in advance.

Upvotes: 0

Views: 133

Answers (2)

user5683823
user5683823

Reputation:

At the time I am posting this, a different answer is already marked as "correct". That answer assigns values 1001, 1002 etc. with no regard to the pre-existing values in col1.

To me that makes no sense. The problem is more interesting if the OP actually meant what he wrote - the new values should follow the pre-existing values in col1, so that while the numbers 1001, 1002, 1003 etc. are not the same as the pre-existing values, they still preserve the pre-existing order.

In Oracle, row_number() is not allowed in a straight update statement. Perhaps the simplest way to achieve this task is with a merge statement, as demonstrated below.

For testing I created a table with two columns, with equal values before the update, simply so that we can verify that the new values in col1 preserve the pre-existing order after the update. I start with the test table, then the merge statement and a select statement to see what merge did.

create table t (col1, col2) as
  select  3,  3 from dual union all
  select  4,  4 from dual union all
  select 16, 16 from dual union all
  select  7,  7 from dual union all
  select  1,  1 from dual
;

Table T created.


merge into t
  using ( select rowid as rid, 1000 + row_number() over (order by col1) as val
          from   t) s
  on    (t.rowid = s.rid)
when matched then update set col1 = val;

5 rows merged.


select * 
from   t
order  by col1
;

   COL1    COL2
------- -------
   1001       1
   1002       3
   1003       4
   1004       7
   1005      16

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In Oracle, the simplest method might be to create a sequence and use that:

create sequence temp_seq_rownum;

update tab1
   set col1 = 1000 + temp_seq_rownum.nextval; 

drop sequence temp_seq_rownum;

Upvotes: 1

Related Questions