Add column in position?

[mikerh9]

When I use 'alter' to add a column into table. The column is added to the end of the table.

So when I do 'desc table', the added column is at the end of the table. Is there a way to add a column into some position?

Like if table has 5 columns and I want to ad another column in position 2. Is it possible to do it?

Thanks

[tekky]

This took 2 minutes to find in the manual....

[LordShryku]

ALTER TABLE table
        ADD field fieldtype 
      AFTER field_to_add_after;

[tekky]

bah Shryku making me look bad :p

giving the answer so easily 😃

[LordShryku]

We posted at the same time. Sorry, I'll be more of a hard ass next time :p

I usually give links to the manuals too. This time, it was just easier to type the three lines instead of finding the link

[tekky]

hehe, doh, didnt notice the time stamps were the same (didnt look this time :eek: )

[mikerh9]

I looked on the page. Here what I want to do;

The table already has data. So I want to move column to another position without loosing data.

The table:
mysql> desc album;
+-------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+------------+----------------+
| id | int(10) | | PRI | NULL | auto_increment |
| title | char(40) | YES | | NULL | |
| description | char(100) | YES | | NULL | |
| date | date | YES | | 0000-00-00 | |
+-------------+-----------+------+-----+------------+----------------+

But when I try to do that I get the error message. I don't know if the problem related to the Server version: 3.23.58

mysql> alter table album modify column date after title;
ERROR 1064: You have an error in your SQL syntax near 'after title' at line 1

[LordShryku]

alter table album modify column date after title;

1) Well, you're not modifying. You're adding. See my original post.
2) Is date the field name or the field type?

[mikerh9]

Originally posted by LordShryku
alter table album modify column date after title;

1) Well, you're not modifying. You're adding. See my original post.
2) Is date the field name or the field type?

I already added the column and the column has data. So now I need just move the column.

"data" is field name

Thanks a lot

[mtimdog]

You received an error because you wrote in your query date, not data....

btw, If it was date, I'm sure LordShryku would have told you that date is a reserverd word so you'd need to backtick like date.

[LordShryku]

Actually, in MySQL's cough wisdom cough, they're not reserved words. They are in SQL standard though.

http://www.mysql.com/doc/en/Reserved_words.html
The following symbols (from the table above) are disallowed by SQL-99 but allowed by MySQL as column/table names. This is because they are very natural names and a lot of people have already used them.

* ACTION
* BIT
* DATE
* ENUM
* NO
* TEXT
* TIME
* TIMESTAMP [/quote]

[mtimdog]

The table above being reserved words...since they are "(from the table above)"...I'm assuming they're reserved words as well.
Not to mention the fact that they aren't in the table above, but purport to be??

[LordShryku]

Check the link. There's a bigger table above that text that contains the reserved words. The list I provided are reserved words according to the SQSL standard, but MySQL doesn't use them as reserved words because they think these words are too common, and should be used as field names.

[mtimdog]

re-read last post...

[LordShryku]

Hrmmm...guess we're saying the same thing....
Sorry.

MySQL does this with a lot of things though. They create their own rules. For anyone who actually uses MySQL like this, just hope that you never have to switch to another database, because you're tables will be all out of whack.