Artur  Arsalanov Artur Arsalanov - 7 months ago 12
SQL Question

How to use alter modify properly?

I have table like this:

+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| car_id | int(11) | NO | PRI | NULL | auto_increment |
| vin | varchar(20) | YES | | NULL | |
| color | varchar(10) | YES | | NULL | |
| year | varchar(4) | YES | | NULL | |
| make | varchar(10) | YES | | NULL | |
| model | varchar(15) | YES | | NULL | |
| price | decimal(10,3) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+


and the content of the table:

+--------+------+-------+------+----------+----------+-----------+
| car_id | vin | color | year | make | model | price |
+--------+------+-------+------+----------+----------+-----------+
| 1 | NULL | gray | 1998 | Porsche | Boxter | 17992.540 |
| 2 | NULL | NULL | 2000 | Jaguar | XJ | 15995.000 |
| 3 | NULL | red | 2002 | Cadillac | Escalade | 40215.900 |
+--------+------+-------+------+----------+----------+-----------+


when I want (for example)to put price column after color column:

alter table car_table modify column price after color;


it says:


mysql> alter table car_table modify column price after color; 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 'after color' at line 1


it doesn't work. What's wrong?

Answer

use the below query You have missed to include the datatype,

ALTER TABLE car_table MODIFY price decimal(10,3) AFTER color;