mysql AFTER option - someone with an example?

hi, has someone used ALTER TABLE to move a field possition with the AFTER option ( http://dev.mysql.com/doc/refman/5.0/en/alter-table.html )?

when i try
ALTER TABLE mytable MODIFY field3 AFTER field1
i get an syntax error message. same if i use CHANGE instead of MODIFY. any ideas?

 

Hi,

Please paste, the syntax error message.

Thanks,

 

thanks, i do in 10h when im at work again - dont have access to the db from here

 

ok, the query i try to execute is:

ALTER TABLE `data_1` change `your_age` AFTER `Your_gender`

Code (markup):

the error msg is not really helpfull, its just ""you have an error in your SQL Syntax near `your_gender`.

tried it also without the ` and with MODIFY instead of CHANGE, same effect...

 

If I am not remembering wrong, you can't use Alter with AFTER.
You may use AFTER with INSERT.
You can Insert A new field with after, Update new field with old field, than delete old field.

 

Hi,

Seems you're missing the column definition:

ALTER [IGNORE] TABLE tbl_name MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

Code (markup):

Try this:

ALTER TABLE `data_1` MODIFY your_age TINYINT NOT NULL AFTER Your_gender

Code (markup):

 

yeah, thanks, works if you specify the datatype again. thought you dont need it since i only wanna change the col position, not the datatype. rep added

(actually i already figured that out 12 hours ago by myself, but i was to busy to post the solution )