changign column order

[rs51]

hi
i read this in the manual:

If you want to change the order of columns anyway, you can do it as follows:
Create a new table with the columns in the right order.
Execute INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.
Drop or rename old_table.
ALTER TABLE new_table RENAME old_table.

And also read this:

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]

but somehow i cant get it working with 2nd option, while first is very laborious.
Can anyone come with a clearer idea?
thanks

 

[sleipnir214]

The first way is by far and away the quickest and easiest way to do it.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rs51]

ok, thanks
maybe i'm too tired/lazy to write&run the apropriate script

 

[sleipnir214]

How many columns are you trying to move? If it's just one, either method will require the same number of queries.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rs51]

what i dont grasp/understand from second way is how the command is written
exemple (of how doesnt work)
alter table Turma add collumn Id
int not null auto_increment primary key FIRST;

 

[sleipnir214]

Define "doesn't work" as you use it in this context. I assume you're getting an error -- what is that error?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rs51]

amazing!!!
worked!!!
mysql> use test;
Database changed
mysql> alter table turma add column Id
-> int not null primary key FIRST;
ERROR 1060: Duplicate column name 'Id'
mysql> alter table turma drop column id;
Query OK, 5 rows affected (0.18 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> alter table turma add column Id
-> int not null primary key FIRST;
ERROR 1062: Duplicate entry '0' for key 1
mysql> alter table turma add column Id
-> int not null auto_increment primary key FIRST;
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from turma;
+----+-----+------+----------+----------+
| Id | Ano | Nome | Epoca_id | Ciclo_id |
+----+-----+------+----------+----------+
| 1 | 5 | A | 6 | 1 |
| 2 | 5 | B | 6 | 1 |
| 3 | 7 | A | 6 | 2 |
| 4 | 7 | B | 6 | 2 |
| 5 | 7 | l | 6 | 2 |
+----+-----+------+----------+----------+
5 rows in set (0.00 sec)
thanks a lot