How do I Alter Table Column datatype on more than 1 column?

mysql alter-table
JoJo picture JoJo · Sep 22, 2010 · Viewed 111k times · Source

For example:

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

The above however does not work. I am using MySql 5.x

Answer

Daniel Vandersluis picture Daniel Vandersluis · Sep 22, 2010

ALTER TABLE can do multiple table alterations in one statement, but MODIFY COLUMN can only work on one column at a time, so you need to specify MODIFY COLUMN for each column 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 attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.

Related questions

How to delete a column from a table in MySQL

Given the table created using: CREATE TABLE tbl_Country ( CountryId INT NOT NULL AUTO_INCREMENT, IsDeleted bit, PRIMARY KEY (CountryId) ) How can I delete the column IsDeleted?

Read More
Error renaming a column in MySQL

How do I rename a column in table xyz? The columns are: Manufacurerid, name, status, AI, PK, int I want to rename to manufacturerid I tried using PHPMyAdmin panel, but I get this error: MySQL said: Documentation #1025 - Error on …

Read More
Alter a MySQL column to be AUTO_INCREMENT

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after the fact. I have tried the following SQL, but got a syntax error notification. ALTER TABLE document ALTER COLUMN document_id AUTO_INCREMENT Am …

Read More