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 6 of 6
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Moving Fields Up/Down

    This is me just being picky but can I move fields up or down in my table? My table looks like this I want to move 1 and 2 up...

    3 zsnfiCr0
    4 1WESIC2t
    1 YEHmyFsb
    2 g5yCxSQt
    5 vIlH9HrC
    6 r5jIexHb
    7 43Pw7LtL
    8 k0BHs7a1
    9 WxxiRNdb
    10 OivjIiUP

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    If the 3,4,1,2,5... are your Primary ID, then no, not without pulling out the data and/or rebuilding the index. Even a REPAIR then OPTIMIZE will not change the index.

    What you can do:

    1) Drop the index then rebuild it. -- By far the most recommended option.

    Code:
    ALTER TABLE `table_name` DROP PRIMARY KEY, ADD PRIMARY KEY(`id_field`);
    2) Update the records. !!!NOT RECOMMENDED!!! May or may not work, depending on your table status (how many keys, how many records,etc). Plus, it takes multiple SQL statements to accomplish.

    Code:
    UPDATE `table_name` SET `ID_field` = 1000 WHERE `some_other_field` = 'YEHmyFsb';
    UPDATE `table_name` SET `ID_field` = 1001 WHERE `some_other_field` = 'g5yCxSQt';
    UPDATE `table_name` SET `ID_field` = 1 WHERE `some_other_field` = 'zsnfiCr0'; 
    UPDATE `table_name` SET `ID_field` = 2 WHERE `some_other_field` = '1WESIC2t'; 
    UPDATE `table_name` SET `ID_field` = 3 WHERE `some_other_field` = 'YEHmyFsb';
    UPDATE `table_name` SET `ID_field` = 4 WHERE `some_other_field` = 'g5yCxSQt';
    3) Remove those two rows, update the ID fields, reinsert rows, update ID fields. !!!NOT RECOMMENDED!!! Again, this takes multiple SQL statements and may mean that you get 3,4,5,6,7,8,9,10,1,2, which of course does not solve your problem.

    4) Use NOTEPAD or TEXTEDIT to copy the data out, truncate the table, enter the data back in. Long hard way, but accomplishes what you want.

    Take your pick...
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Great thanks for all the options! I tried the first option but it didn't change anything... Any ideas?

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Also when I add to the table, and then delete all fields in the table if I add more data, the auto_increment starts from where it was previously how do I reset this?

  • #5
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Did it actually drop the index?

    If it didn't change anything, then it probably did not. Might have thrown an error that 'ONLY ONE AUTO_INCREMENT PER TABLE AND MUST BE DEFINED AS KEY' or something to that effect.

    So.... To fix this, we use:

    Code:
    ALTER TABLE `table_name` DROP `id_field`, DROP PRIMARY KEY;
    ALTER TABLE `table_name` ADD `id_field` BIGINT() UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(`id_field`);
    Review:

    1) This will drop the id_field column from the table as well as the key/index.

    2) This will re-add the id_field column as the FIRST column (not do this action first, as this is set by the order of the commands), then add the PRIMARY KEY index on said column.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    And as a side note, this time me being picky, you are technically moving RECORDS, not FIELDS.

    Each Column is a FIELD and each Row is a RECORD.

    You are looking to move the two RECORDS, not two FIELDS.

    I know, I know... I'm being picky, but there is a difference.


  •  

    Posting Permissions

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