altering column order

[studioceasar]

Hello all,
Is there an easy way to edit the order of my colums in a table?
Presently, my colums are: cust_num, name, email, location, bday_day, bday_month, bday_year, state

I would like to list the "location" column after "state".

Can anyone offer a simple suggestion.

Thanks in advance.
C-

[nav33n]

Very simple.

ALTER TABLE tablename ADD columnname datatype AFTER columnname

[studioceasar]

Thanks for your response. I should have mentioned that my column already has data in it. I need to move the column as well as the data into a new position so that I can then export the table and then merge them into another file.

Any suggestions appreciated.
Thanks again,
C-

[nav33n]

Hey, sorry. I misunderstood the question.

/*
ALTER TABLE tablename MODIFY COLUMN columnname AFTER columnname 
*/
Eg. ALTER TABLE members MODIFY COLUMN date_of_birth date AFTER username

Cheers!

[studioceasar]

Thanks again for your help.

This is what I entered as a statement:

ALTER TABLE clown_contest MODIFY COLUMN `location` AFTER `state`

This is what I'm getting back:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AFTER `state`' at line 1

What am I missing?

C-

[nav33n]

You forgot the datatype! (doh, even I forgot it in the syntax).
Sorry, here is the right syntax.

ALTER TABLE tablename MODIFY COLUMN columnname datatype AFTER columnname

This works for sure!

[studioceasar]

Thanks for your help again. Unfortunately, it doesn't seem to be working.
This is the latest message I get:
SQL query:

ALTER TABLE tablename MODIFY COLUMN `location` datatype AFTER `state`

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datatype AFTER `state`' at line 1

Any insight is greatly appreciated!!

[nav33n]

Dude, datatype as in int, varchar, date, datetime, etc.

ALTER TABLE clown_contest MODIFY COLUMN `location` varchar(40) AFTER `state`

Clear ? :)

[studioceasar]

Thanks!!!

[nav33n]

You are welcome :)