SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    adjusting a numeric col

    Hi,
    I have a table column which is a INT and represents a sequence number for the output of the records from that table.

    when I delete some records the seq nos will be for example, 1, 3, 7, 8, 9

    I need your advice on what sort of query I need to make them again read like 1,2,3,4,5

    I can identify the rows which exists and which have a sequence number

    Code MySQL:
    select 
      col1
    , col2
    from external_links
    where business_id = 2

    which can return 1, 3, 7, 8, 9

    How to reset them (consecutively) to 1, 2, 3, 4, 5 is what i need help with please.

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    basically, it is to take all sequence numbers and to reset them in a numeric fashion - keeping the sequence in the same order.

    bazz

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, coz it seems that an empty number means an empty textarea on the web page and post-processing (to compensate), also seems to take a lot more effort for many web views, than does one re-adjustment of the numbers at adjustment time.

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    btw, it's not an auto_increment column just a number that is meant to enforce a specific sequence.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you are retrieving these numbers from a table column? and they are in sequence, although not necessarily consecutive?

    is there an ORDER BY clause in your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz
    well, coz it seems that an empty number means an empty textarea on the web page and post-processing (to compensate), also seems to take a lot more effort for many web views, than does one re-adjustment of the numbers at adjustment time.
    Without knowing all the details that seems like a logical error/overlook on behalf of the application code. When the result set is ordered by the sequence column regardless of gaps the ordering *should* be the same.
    The only code I hate more than my own is everyone else's.

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do you insert those integers in your sequence column? Is there some sort of a trigger or is it application controlled?


Bookmarks

Posting Permissions

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