switching columns in mysql

[alijsh]

Is there a way to switch the placement of two columns in a table?

[Sxooter]

Normally, you have to dump the table and edit by hand and reload it.

You can try something like this:

select field2, field1 into newtable from oldtable;

Where field2 and field1 are in the order you want them to appear in.

[statrat]

if you have >mysql 4.0.1

you can try

ALTER TABLE tbl_name MODIFY column_name [create_definition] AFTER other_column_name

or

ALTER TABLE tbl_name CHANGE column_name column_name AFTER other_column_name

http://www.mysql.com/doc/en/ALTER_TABLE.html
"From MySQL Version 4.0.1, you can also use the FIRST and AFTER keywords in CHANGE or MODIFY. "

[alijsh]

I've tried all thrre ways, and they give me errors

mysql> select col into newt from oldt;
ERROR 1064: You have an error in your SQL syntax near 'newt from oldt' at line 1