What can another keyword be used instead of MODIFY to modify the column/s of MySQL table?


We can use keyword CHANGE to modify the column/s of an existing table. With CHANGE keyword we can change the name of the column and its definition both. Its syntax would be a bit different from the syntax of ALTER TABLE with MODIFY keyword.

Syntax

Alter table table_name CHANGE old_columnname1 new_columnname1 datatype, CHANGE old_columnname2 new_columnname2 datatype… CHANGE old_columnnameN new_columnname datatype);

Example

In the example below, with the help of CHANGE keyword in ALTER Command, the name and size of the columns ‘City’ and ‘RollNo’ have been modified.

mysql> Alter table Student CHANGE Rollno Id int, CHANGE City Place Varchar(10);
Query OK, 5 rows affected (0.40 sec)
Records: 5 Duplicates: 0 Warnings: 0

But, if we only want to resize the column with CHANGE keyword then write old column name both the times after keyword CHANGE along with new size. The description is given in the example below

mysql> Alter table Student CHANGE Email Email Varchar(30);
Query OK, 5 rows affected (0.33 sec)
Records: 5 Duplicates: 0 Warnings: 0

Updated on: 29-Jan-2020

197 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements