How do I alter table column datatype on more than 1 column at a time 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.

Rishi Rathor

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• How do I alter a MySQL table column defaults?
• How to sort more than one column at a time in MySQL?
• Alter a table column from VARCHAR to NULL in MySQL
• How do I add more members to my ENUM - type column in MySQL?
• How do I update the decimal column to allow more digits in MySQL?
• Alter a MySQL column to be AUTO_INCREMENT?
• How to alter the data type of a MySQL table’s column?
• How to add column using alter in MySQL?
• Update a column based on another MySQL table’s column
• How do I see what character set a MySQL database  / table / column is?
• How do I see all foreign keys to a table column?
• MySQL query to include more than one column in a table that doesn't already exist
• How can I remove every column in a table in MySQL?
• How do I modify a MySQL column to allow NULL?
• How do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column in MySQL?