Alter Table Column Datatype on Multiple Columns in MySQL

To add more than 1 column with ALTER table command, you can use MODIFY column command. The syntax is as follows −

alter table yourTableName
modify column yourColumnName1 dataType,
modify column yourColumnName2 dataType,
.
.
.
modify column yourColumnNameN dataType

To understand the above syntax, let us create a table. The following is the query −

mysql> create table AddColumn
   −> (
   −> StudentID int,
   −> StudentName varchar(200)
   −> );
Query OK, 0 rows affected (0.49 sec)

Above we have two columns in the table “AddColumn”. In this we will see how to modify more than one column datatype −

mysql> alter table AddColumn
   −> modify column StudentName varchar(300),
   −> modify column StudentId varchar(50);
Query OK, 0 rows affected (1.95 sec)
Records: 0 Duplicates: 0 Warnings: 0

Check whether the modified column added successfully in the table or not. The query is as follows −

mysql> desc AddColumn;

The following is the output −

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| StudentId   | varchar(50)  | YES  |     | NULL    |       |
| StudentName | varchar(300) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Now look at the sample output, both column have been changed.