Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2011
    Location
    Space
    Posts
    22
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Condense a table

    Hi.
    Is there any way to condense down the primary key of a table if there are missing numbers?
    For example, the primary key is a number from 1 to 100 and it has random numbers missing from rows being deleted.
    ID DATA
    1 codingforums.com
    2 codingforums.com
    7 codingforums.com
    9 codingforums.com

    So it would end up reading:
    ID DATA
    1 codingforums.com
    2 codingforums.com
    3 codingforums.com
    4 codingforums.com
    Last edited by Celestial; 10-06-2011 at 12:52 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Code:
    CREATE TEMPORARY TABLE clone LIKE mytable;
    
    INSERT INTO clone
    SELECT * FROM mytable;
    
    TRUNCATE mytable;
    
    INSERT INTO mytable ( all, fields, except, auto, increment, field )
    SELECT all, fields, except, auto, increment, field
    FROM clone
    ORDER BY auto_increment_field;
    
    DROP TEMPORARY TABLE clone;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    The difficulty with that, if it's not obvious, is that it is slow and clumsy and you better not be making any changes to the table in some other thread while this is going on.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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