Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# update statement with sequence.nextval

Posted on 2003-03-25
Medium Priority
24,084 Views
my sequence is just cycling two numbers?

SEQUENCE_OWNER    SEQUENCE_NAME         MIN_VAL  MAX_VAL INCRE_BY C O CACHE_SIZE LAST_NUMBER
----------------- --------------------- ------- -------- -------- - - ---------- -----------
DBA            GROUP_SEQUENCE              2        5        1    Y Y          0           5

UPDATE table
SET group_id = group_sequence.nextval
where column_name = 'some_number';

my feeling is that update is some how causing sequence to increment by two or something crazy.
NOCACHE and ORDER have both been tried for cache settings.
but all my tests have proven my feeling wrong.

any thoughts?

0
Question by:brenthel3
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 8

Expert Comment

ID: 8206540

UPDATE table
SET group_id = group_sequence.nextval
where column_name = 'some_number';

when you use update statement, it always update your table records one by one. each records use group_sequence.nextval
will have different value.

let's say, your sequence current value is 2, and you have 2 records by your "where column_name = 'some_number';" clause, the group_id will be 3,4; if you run it again,
the group id will be 5,6..;

let's say, your sequence current value is 2, and you have 3 records by your "where column_name = 'some_number';" clause, the group_id will be 3,4,5;

if you want all the records have same value, you should do like this:

--assume Vnext is a variable
.....
select group_sequence.nextval into Vnext Vfrom dual;
UPDATE table
SET group_id = group_sequence.currval
where column_name = 'some_number';
....

hope it helps.

0

Author Comment

ID: 8206593
i know how curval and nextval work.  maybe i wasn't clear...here are my results
2 4 2 4 2 4 2 4 2

here is what i am wanting
2 3 4 5 2 3 4 5 2
0

LVL 8

Expert Comment

ID: 8207364

how many records will be updated each time?

UPDATE table
SET group_id = group_sequence.nextval
where column_name = 'some_number';
0

LVL 8

Expert Comment

ID: 8207481

how many records will be updated each time?

UPDATE table
SET group_id = group_sequence.nextval
where column_name = 'some_number';
0

LVL 8

Expert Comment

ID: 8207529
I did some test.
maybe your problem is: every time you have 2 records to be updated.

the group_id looks like:
2 (3) 4 (5) 2 (3) 4 (5) 2...

or show us all the records(group_id,column_name) in your table.
0

Author Comment

ID: 8211088
only one row gets updated at a time. even if i did do two records the count for each group would be balanced, instead it looks like this.
group 2 = 50
group 3 = 0
group 4 = 50
group 5 = 0

but yes depending on where the sequence is currently at sometimes it will be 3 5 3 5 3.
select group_sequence.nextval from dual; result is 4
then the groups get updated...
2 4 2 4 2 4 2 4 2

This tested successfully in sqlplus.  However I'm using PHP to do the updates.  guess the problem exists somewhere on the PHP side.
thanks anyway.
0

LVL 8

Expert Comment

ID: 8213789
check you code, make sure there is no other place using    "group_sequence.nextval" or "select group_sequence.nextval..."
0

LVL 2

Expert Comment

ID: 8263157
Sometimes it is interesting to turn on SQL TRACE for the session or entire database.  That way you can see what SQL is being executed to support the SQL statement you are running.

I don't know anything about PHP, but I have been shocked to see the SQL that is generated from a simple select statment issued via OLE or ODBC.  It will generate 10 SQL statments for a single select.  Multiple queries against the target table, queries against DBA_ views and more.  I realize why it had to be done.
0

LVL 22

Expert Comment

ID: 10216429
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/No Refund.

Henka
EE Cleanup Volunteer
0

Accepted Solution

SerCouWisMOD earned 375 total points
ID: 10278513
*****
As recommended this question was closed and  points were not refunded by:

SerCouWisMOD
Community Support Moderator
*****
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacyâ€¦
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with genâ€¦
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode aâ€¦
This video shows how to recover a database from a user managed backup
###### Suggested Courses
Course of the Month7 days, 6 hours left to enroll