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
10
Medium Priority
?
24,084 Views
Last Modified: 2013-12-11
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

sorry about the formatting...


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
Comment
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
  • Learn & ask questions
10 Comments
 
LVL 8

Expert Comment

by:Danielzt
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

by:brenthel3
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

by:Danielzt
ID: 8207364

how many records will be updated each time?

UPDATE table
SET group_id = group_sequence.nextval
where column_name = 'some_number';
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 8

Expert Comment

by:Danielzt
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

by:Danielzt
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...

please check your table whether it has records with group_id 3,5,3,5....

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

Author Comment

by:brenthel3
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

by:Danielzt
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

by:mszacik
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

by:Helena Marková
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.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer
0
 

Accepted Solution

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question