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-

Very simple.

ALTER TABLE tablename ADD columnname datatype AFTER columnname

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-

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!

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-

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!

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

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

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

Clear ? :)

You are welcome :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.