CODEWITHSUNDEEP
oracle-database
user4993731
user4993731

Reputation:

Auto increment without sequence

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            |
     -----------------------------------------------------------------

Upvotes: 0

Views: 5055

Answers (1)

Mark Harrison
Mark Harrison

Reputation: 304444

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?

Upvotes: 2

Related Questions