SQL - ALTER TABLE MODIFY COLUMN command

<<Previous - SQL ALTER TABLE DROP COLUMN

Next - SQL ALTER TABLE RENAME>>

SQL ALTER TABLE statement is used to alter the structure of the table. By using this ALTER TABLE, you can add, delete or modify the column of the existing table.

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | int         | YES  |     | NULL    |       |
| Name   | varchar(20) | YES  |     | NULL    |       |
| Age    | int         | YES  |     | NULL    |       |
| City   | char(20)    | YES  |     | NULL    |       |
| Dept   | char(10)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.14 sec)

The syntax to change or modify the data-type of the column in the existing table.

ALTER TABLE  table-name MODIFY column-name data-type;

For example, consider the above [student] table in which we want to modify the data-type of [City] column. Following is the query to change the data-type.

mysql> ALTER TABLE student MODIFY City varchar(20);
Query OK, 2 rows affected (2.76 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | int         | YES  |     | NULL    |       |
| Name   | varchar(20) | YES  |     | NULL    |       |
| Age    | int         | YES  |     | NULL    |       |
| City   | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.12 sec)

<<Previous - SQL ALTER TABLE DROP COLUMN

Next - SQL ALTER TABLE RENAME>>