2

Edit 3 : I was following Head First SQL , where it was said we can use keywords such as First , Second etc etc. I stand clarified by @StanMcgeek that one can only use First , Last and After.

Before the reader gets friendly with the down-vote , I'd like to mention that

  1. I know column Re-arrangement is generally just an aesthetic issue.
  2. I'm doing it for learning purpose only

That being said , this is what i was trying to do:

hooptie is a table , with some values , i'd like to rename it to car_table and add some more columns to it. Below is the renamed hooptie , and my attempts at adding and re-arranging the order of a few columns. The output is -tee'd into a text file. text is easier to post than screenshots :) 
--------------
select * from car_table
--------------

+--------+------+----------+----------+-----------+
| color  | year | make     | mo       | howmuch   |
+--------+------+----------+----------+-----------+
| silver | 1998 | Porsche  | Boxter   | 17992.539 |
| NULL   | 2000 | Jaguar   | XJ       | 15995.000 |
| red    | 2002 | Cadillac | Escalade | 40215.898 |
+--------+------+----------+----------+-----------+

--------------
alter table car_table
add column car_id int not null auto_increment first,
add column VIN varchar(16) second,
modify make varchar(20) third,
change mo model varchar(20) fourth,
modify color varchar(20) fifth,
modify year varchar(4) sixth,
change howmuch price decimal(7,2) last
--------------

After doing the above alterations , i do another select * from car_table , but the table has remained the same. I suppose that randomly saying first , second , third etc dont really cut it . Can anyone explain what's going on here ?

Edit1: error message that i'm getting says :

ERROR 1064 (42000): 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 'second,

I dont like mysql error messages too much .. they seem a little vague to me.

Edit2: I have used keywords like After , and i know it works. I wanted to understand how first,second etc keys work.

p.s : I'm following the Head-First-SQL book , the data is taken from there too.

Somjit
  • 2,503
  • 5
  • 33
  • 60
  • check your error message. – Rob Oct 04 '13 at 07:08
  • Logically the order of the field names are irrelevant. – Mark Oct 04 '13 at 07:10
  • try this: http://stackoverflow.com/questions/14767174/modify-column-vs-change-column/14767467#14767467 – John Stein Oct 04 '13 at 07:11
  • @somjitnag I recommend downloading mysql workbench. After you have connected to your database, right click on the table you wish to change and click alter table. Make the relevant changes (ie change name, change column order, add columns) via the gui and click apply, this will give you the relevant sql that make the changes you have done. Great for learning sql commands. – The Humble Rat Oct 04 '13 at 07:17
  • i have that , i can do that anytime i want to. As i said , i'm doing this for learning purposes , and i prefer the command line for that. – Somjit Oct 04 '13 at 07:19

1 Answers1

2

Try something like this:

ALTER TABLE 
    table_name
MODIFY COLUMN 
    column_to_move data_type
AFTER 
    other_column_name
Wintermute
  • 1,501
  • 17
  • 22
  • I had tried `after` , and i know it works , i wanted to know how to use these `first` , `second` etc keywords... if thats worth the trouble.. – Somjit Oct 04 '13 at 07:16
  • 1
    @Somjit Nag - Oh, didn't get that from the question. Then you are not understanding this one correctly: you have only FIRST keyword to add column before everything; do not use FIRST - it will be last; if you want it somewhere in between - use AFTER – Wintermute Oct 04 '13 at 07:21
  • That is the answer i was looking for. :) Apparantly , Head First SQL says that i can use `First` , `Last` and all things in between. Thanks for clarifying that. That book needs a reprint i suppose. – Somjit Oct 04 '13 at 07:23
  • bdw , can we use `before` ? By the way , i think you meant "do not use FIRST first , it should be at the last.." ? – Somjit Oct 04 '13 at 07:27
  • @SomjitNag - nope, it'd be syntactically excessive. Here's the reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html – Wintermute Oct 04 '13 at 07:29