04-06-2007, 04:18 PM
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...
04-06-2007, 04:39 PM
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.
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.
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... :)
04-06-2007, 04:43 PM
Great thanks for all the options! I tried the first option but it didn't change anything... Any ideas?
04-06-2007, 04:48 PM
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?
04-06-2007, 04:51 PM
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:
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`);
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.
04-06-2007, 04:56 PM
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. :)