lwk@caen.engin.umich.edu (Woody Kellum) (08/04/90)
I have an application in Oracle that requires records to have a sequence
number associated them. I want to be able to insert new records and move
old records around within the sequence, and then re-number the records
periodically, keeping the same order, so that the sequence numbers don't
get to hairy. I want something like:
UPDATE ORDERD_TABLE SET ORDERNO = MY_$SEQ.NEXTVAL ORDER BY ORDERNO;
The above is not a valid Oracle sql statement. There must be a way to do
this, but I haven't thought of it yet. Any ideas?
--
Woody Kellum Internet: lwk@caen.engin.umich.edu
srm@occrsh.ATT.COM (08/05/90)
/* ---------- "Sequencing in Oracle" ---------- */ >old records around within the sequence, and then re-number the records >periodically, keeping the same order, so that the sequence numbers don't >get to hairy. I want something like: According to one of the Oracle personnel that I talked to about this very situation, he said that this feature of sequencing should not be used if the sequence number is required to be in any order. From what I gather, you are attempting to create an intelligent key. My suggestion to you is to either copy the data from ORDERD_TABLE to OLD_ORDERD_TABLE, delete/drop ORDERD_TABLE and insert from OLD_ORDERD_TABLE into ORDERD_TABLE -- or -- control your own sequencing by creating a table with a list of available sequence numbers. When one sequence number is dropped from the table, insert it back into the SEQUENCE_NUMBERS table (or have a 2-column table and NULL out the "used" flag). -- Steven R. McMaster UNIX(R) mail: ...!uunet!att!occrsh!srm AT&T Network Systems Oklahoma City Works Any opinions expressed in the message above are srm@occrsh.att.com mine, and not necessarily AT&T's.