#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    30
    Rep Power
    15
    Here is the senerio. I have 1 table (members) and an primary ID that is autoincrmenting. I rely on this ID to be in order i.e 1,2,3,4,5..... If I go and say do this

    Delete from members where (ID = 3);

    My order now reads... 1,2,4,5....
    Is there anyway to execute a specific MySQL command to make those numbers sync up after deleting the spcific row? Thanks!!!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    If only one row is deleted and you know which row it is, then

    > update table
    > set id=id-1
    > where id>3;

    should work
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    London, England
    Posts
    251
    Rep Power
    15
    The only reason I can think that you would want to do this is if you need to keep track of the order in which the records were entered.

    If so, you'd be better off creating a field with the type DATETIME and assigning NOW() into it when the record is created.

    Then you can sort by this field and even assign order numbers on the fly in your output without worrying about your autoincremented key being complete.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    30
    Rep Power
    15
    Just so everyone knows I finally figured out what I was doing. I ended up using a limit statment to grab the next 10 records. I changed my code around to only use the autoincrementing ID for updating, not displaying. Thanks to everyone who gave me ideas!

Similar Threads

  1. Easiest way to create an auto incrementing column?
    By jpenn in forum Firebird SQL Development
    Replies: 5
    Last Post: December 29th, 2006, 04:43 AM
  2. Bob's Auto Parts
    By nicknike in forum PHP Development
    Replies: 1
    Last Post: January 19th, 2004, 05:40 PM
  3. Creating table from auto incremented integer
    By Gamblor in forum MySQL Help
    Replies: 2
    Last Post: May 1st, 2002, 01:12 PM
  4. Insert and auto incrementing primary keys
    By Wiggums in forum PHP Development
    Replies: 2
    Last Post: June 12th, 2001, 05:44 PM
  5. Setting auto increment
    By cube96 in forum MySQL Help
    Replies: 3
    Last Post: March 18th, 2001, 09:45 AM

IMN logo majestic logo threadwatch logo seochat tools logo