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.
Published on 11-Jan-2019 08:21:42
