How to change the data type of a table column by more than 1 column?

Question

For example:

ALTER TABLE webstore.Store MODIFY COLUMN ( ShortName VARCHAR(100), UrlShort VARCHAR(100) ); 

However, the foregoing does not work. I am using MySql 5.x

Answer 1

ALTER TABLE can make several table changes in one expression, but MODIFY COLUMN can only work one column at a time, so you need to specify MODIFY COLUMN for each column that you want to change:

 ALTER TABLE webstore.Store MODIFY COLUMN ShortName VARCHAR(100), MODIFY COLUMN UrlShort VARCHAR(100); 

Also note this warning from the manual:

When you use CHANGE or MODIFY, column_definition must include the data type and all the attributes that should be applied to the new column, except for index attributes such as PRIMARY KEY or UNIQUE. Attributes that are present in the original definition but not specified for the new definition are not carried forward.

Answer 2

Use the following syntax:

  ALTER TABLE your_table MODIFY COLUMN column1 datatype, MODIFY COLUMN column2 datatype, ... ... ... ... ... ... ... ... ... ... 

Based on this, your ALTER command should be:

  ALTER TABLE webstore.Store MODIFY COLUMN ShortName VARCHAR(100), MODIFY COLUMN UrlShort VARCHAR(100) 

Note that:

• There are no second brackets in the MODIFY statement.
• I used two separate MODIFY statements for two separate columns.

This is the standard format of the MODIFY statement for the ALTER command for multiple columns in a MySQL table.

Take a look at the following: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html and Edit multiple columns in one statement