Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change the Next Value in a Sequence

Status
Not open for further replies.

jimmythegeek

Programmer
May 26, 2000
770
US
I want to be able to update the last value in a sequence to the maximum value from a table. If it were a table, I would use this:

Update table1 set lastvalue = (Select Max(ID) from table2)

However my book says to use Alter Sequence, so I tried this:

alter sequence test_seq minvalue (Select Max(ID) from table2)

That doesn't work either. Any help is greatly appreciated.
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?
 
As far as I know, you got to specify an integer to alter the min value or max value of a sequence, you cannot use a select stmt there. So you need to find the max_value from the tbl and plug it in the alter sequence stmt.
 
Hi Jim,

Firstly, the minvalue cannot exceed the current sequence value.
If you want to increment the maxvalue of the sequence, try this:

col cIncByVal noprint new_value uIncByVal
select maxID cIncByVal from table2;
alter sequence test_seq maxvalue &uIncByVal;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top