Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to alter column type of multiple columns in a single MySQL query?
To alter column type of multiple columns in a single MySQL query, the syntax is as follows −
alter table yourTableName modify column yourColumnName 1 yourDataType1, modify column yourColumnName 2 yourDataType2, . . N;
Let us first create a table −
mysql> create table DemoTable ( Id varchar(100), FirstName text, LastName text ); Query OK, 0 rows affected (0.52 sec)
Let us check the description of table −
mysql> desc DemoTable;
This will produce the following output −
+-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | Id | varchar(100) | YES | | NULL | | | FirstName | text | YES | | NULL | | | LastName | text | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 3 rows in set (0.09 sec)
Following is the query to alter column type of multiple columns. Here, we have altered the column type of columns Id, FirstName and LastName −
mysql> alter table DemoTable modify column Id int, modify column FirstName varchar(50), modify column LastName varchar(50); Query OK, 0 rows affected (1.63 sec) Records: 0 Duplicates: 0 Warnings: 0
Let us check the description of table once again −
mysql> desc DemoTable;
This will produce the following output −
+-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | FirstName | varchar(50) | YES | | NULL | | | LastName | varchar(50) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Updated on: 01-Oct-2019
617 Views
Advertisements