0

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-

2
Contributors
9
Replies
10
Views
7 Years
Discussion Span
Last Post by nav33n
0

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-

0

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!

0

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-

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

0

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!

0

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!!

0

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

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

Clear ? :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.