Oracle Sql: How to Read-And-Increment a Field

Oracle SQL: How to read-and-increment a field

In this particular case, a sequence is the right solution as mentioned. But if in some future situation you need to both update something and return a value in the same statement, you can use the RETURNING clause:

UPDATE atable SET foo = do_something_with(foo) RETURNING foo INTO ?

If the calling code is PL/SQL, replace the ? with a local PL/SQL variable; otherwise you can bind it as an output parameter in your program.

Edit: Since you mentioned Perl, something like this ought to work (untested):

my $sth = $dbh->prepare('UPDATE mytable SET idnext = idnext + 1 returning idnext into ?');
my $idnext;
$sth->bind_param_inout(1, \$idnext, 8);
$sth->execute; # now $idnext should contain the value

See DBI.

in oracle select query how to add a number column increment by 2 starting from 1

You know you have rownum available, but take a step back. You're starting with the contiguous sequence 1,2,3,4,5,6,... and you want to generate a sequence of odd numbers 1,3,5,7,9,11,.... So you need to figure out an algorithm that will convert one to the other.

If you say your starting number is n then you want to generate m where m=(2*n)-1.

You can use rownum (or row_number(), etc.) to generate your n values:

select column1, rownum as n
from your_table;

And you can then apply that algorithm:

select column1, (2*rownum)-1 as column2
from your_table;

COLUMN1    COLUMN2
------- ----------
amit             1
siva             3
pyll             5
jane             7
john             9
anna            11
...

With this simple approach the column2 values are not in the same order as the column1 values. You can either use row_number() or rank() instead, with a suitable order by clause; or use a subquery which does the ordering and apply rownum (and this algorithm) outside that:

select column1, (2*rownum)-1 as column2
from (
  select column1
  from your_name
  order by column1
);

or some other variation, depending on the result you want to end up with.

Increment column A when column B changes value

This is easy to do with the dense_rank() function in the query used to fetch the results:

select name, hour, value,
       dense_rank() over (order by name) as row_num
from t;

Note: this will not necessarily assign the values in the order you have given. But each group will get a different value. If you need them in the order given, then you will need to identify the order. SQL tables are inherently unordered, so a column is needed to specify the ordering.

Increment column values based on two columns in Oracle database

The NEW_RO column can be calculated with the analytic function ROW_NUMBER():

select ... ,
       row_number() over (partition by cid order by ro) as new_ro  [, ...]

In your data, there are ties for RO within the same CID. Do you care, in that case, which row gets what NEW_RO value? If, for example, in the case of same RO you also want to (further) order by ID, you can change the above to

select ... ,
       row_number() over (partition by cid order by ro, id) as new_ro  [, ...]

EDIT: I missed the fact that you need to UPDATE the RO values with the NEW_RO values. Analytic functions can't be used in an UPDATE statement (not directly anyway); the MERGE statement is the perfect alternative for this:

merge into test
  using ( select id, 
                 row_number() over (partition by cid order by ro, id) as new_ro 
          from   test
        ) s
        on (test.id = s.id)
when matched then update set ro = s.new_ro
;

Oracle - Insert New Row with Auto Incremental ID

To get an auto increment number you need to use a sequence in Oracle.
(See here and here).

CREATE SEQUENCE my_seq;

SELECT my_seq.NEXTVAL FROM DUAL; -- to get the next value

-- use in a trigger for your table demo
CREATE OR REPLACE TRIGGER demo_increment 
BEFORE INSERT ON demo
FOR EACH ROW

BEGIN
  SELECT my_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

How to create id with AUTO_INCREMENT on Oracle?

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq START WITH 1;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE:

IDENTITY column is now available on Oracle 12c:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

Auto increment in Oracle SQL

If you use double quotes to create an object (and the object name is not all upper case), then you are forced to use double quotes everywhere you refer to it.

So in this case, this would work (notice the double quotes around Account_seq):

INSERT INTO "Account" VALUES ("Account_seq".NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxx', 'user');

That said, using double quotes everywhere is annoying. Instead, consider changing the sequence creation to avoid using double quotes:

CREATE SEQUENCE Account_seq ...

... that way, you can refer to it without double quotes and the sequence name won't be case sensitive.

EDIT

To clarify the case sensitivity part a little bit:

When you double quote an identifier, then Oracle looks for that identifier with that exact casing.

On the other hand, if you don't use double quotes, it will look for the equivalent identifier all upper-cased.

Example:

• "Account_seq": it will look for Account_seq
• "AcCoUnT_SeQ": it will look for AcCoUnT_SeQ
• Account_seq: it will look for ACCOUNT_SEQ
• AcCoUnT_SeQ: it will look for ACCOUNT_SEQ

---

Related Topics