DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: sequence

  1. #1
    Join Date
    May 2002
    Posts
    232
    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
    kavitha

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    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.

  5. #5
    Join Date
    May 2002
    Posts
    232
    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
    kavitha

  6. #6
    Join Date
    Apr 2002
    Posts
    135
    hai,

    create a new sequence

    create sequence seq_new;

    then ,

    update yourtable set field_name=seq_new.nextval;

    Good Judgement comes from Experience.
    Experience comes from Bad Judgement

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width