Auto increment without sequence

Question

I have a select statement that generate set value thereafter I want insert that set of values into another table, MY concern is I'm using select statement in select I'm using one one more select clause((select max(org_id)+1 from org)) where I'm trying to get max value and increment by one but I'm not able get incremented value instead I'm getting same value you can see column name id_limit

select abc,abc1,abc3,abc4,(select max(org_id)+1 from org) as id_limit from xyz

current output    
    -----------------------------------------------------------------
     | abc           |  abc1       |   abc3 | abc4   | id_limit     |
     ----------------------------------------------------------------|
     | BUSINESS_UNIT |    0        |   100  | London | 6            |
     | BUSINESS_UNIT |    0        |   200  | Sydney | 6            |
     | BUSINESS_UNIT |    0        |   300  | Kiev   | 6            |
     -----------------------------------------------------------------

I'm trying to get expected out output

     -----------------------------------------------------------------
     | abc           |  abc1       |   abc3 | abc4   | id_limit     |
     ----------------------------------------------------------------|
     | BUSINESS_UNIT |    0        |   100  | London | 6            |
     | BUSINESS_UNIT |    0        |   200  | Sydney | 7            |
     | BUSINESS_UNIT |    0        |   300  | Kiev   | 8            |
     -----------------------------------------------------------------

Use a sequence. Are `org` and `xyz` supposed to be the same table? How do you know which row gets which value? Are you intending to sort on `abc3`? — Justin Cave, Nov 18 '15 at 18:56
This is exactly why sequences were invented. They are The Solution to this problem. Why would you not want to use a sequence? — Bob Jarvis - Слава Україні, Nov 18 '15 at 19:12
Whatever you call it, your task is the generation of a globally unique identifier. This operation mandates mutual exclusion, otherwise there will always be a (possibly small but always non-zero) chance that competing computations end up with the same result. There are many ways to ascertain mutual exclusion of which sequences are probably the most simple. — collapsar, Nov 18 '15 at 19:35
There are two possible ways of implementing a unique number generation using the `max()` approach: a slow one where only a single transaction at one given time can insert or access the table, or the wrong one were multiple transactions are allowed, but uniqueness is not possible (that's what you have implemented). The **only** scalable and robust way to do this is to use a sequence. Why don't you want to use a sequence? — , Nov 18 '15 at 20:02

score 2 · Answer 1 · edited May 23 '17 at 10:27

2

Yes, in Oracle 12.

create table foo (
  id number generated by default on null as identity
);

https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

In previous versions you use sequence/trigger as explained here:

How to create id with AUTO_INCREMENT on Oracle?

edited May 23 '17 at 10:27

Community

1
1

answered Nov 18 '15 at 19:18

Mark Harrison

297,451
125
333
465

Auto increment without sequence

1 Answers1