sequence

[kavithared]

Hii,i have problem with sequence on a table,I have a sequence
on a column like abc.i have 1000 rows,now seqence number is 1000
my admin deleted 100 rows from 1000rows.So next sequencenumber is 1001.
But my admin asking when ever delete the rows,immediately he wants set sequence numbers
to next number for next insert row.
is there any solution to reset the sequence numbers.
ex fr0m 1000 to 100 rows deleted,deletion is not order numbers,
immidiately to reset to numbers 1to900 in order.
If any one know plss send me examle(procedure or trigger etc)
thanks
kavitha

[TomazZ]

hmm...
Where is the point here of having those sequence numbers at all. If the number won't stay on its record but move around, then what are you numbering here.

[marist89]

Originally posted by TomazZ

Where is the point here of having those sequence numbers at all. If the number won't stay on its record but move around, then what are you numbering here.

I agree, a sequence number is not appropriate here. You will either need to use AQ to setup a queue to grab the max(id) from the table and increment, or write your own queueing mechanism to ensure more than one person won't get the max(id)+1 and try to use it as an id number.

[jgmagnus]

Yeah, shouldn't sequences just be used for uniquely identifying a row? The actual number should be insignificant for anything other than that ...

Still wondering WHY in the world anyone would care if you have everything in PERFECT sequence ... it's not like you need to look at the number or anything, just get the sequence number, then call the row.

[kavithared]

Hii,all Is there any update sql script to reset the seqence numbers in order,because i was deleted some rows from my table.
plss if any one know respond soon,this is very need for me.
many thanks
kavitha

[sona]

hai,

create a new sequence

create sequence seq_new;

then ,

update yourtable set field_name=seq_new.nextval;

[marist89]

If you're asking for a one-time fix, then just re-create the sequence.

However, we're trying to tell you that there are some serious design issues in your application that need to be addressed.

[pando]

I am not sure but answering to this peep seems useless, at the end all he wants is the command

that´s why I dont even bother to look his posts

[marist89]

Originally posted by pando
I am not sure but answering to this peep seems useless, at the end all he wants is the command

that´s why I dont even bother to look his posts

I guess I should start looking for some "special" individuals myself...