How can I change the name of an existing column from a MySQL table?

MySQLMySQLi Database

We can change the name of a particular existing column from a MySQL table by using CHANGE statement along with ALTER statement. Its syntax would be as follows −

Syntax

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

Here, 

  • table_name is the name of the table from which we want to delete the column.
  • Old_column_name is the name of the column which is to be changed.
  • new_column_name is the name of the column which has to be given to the old column.

Example

In this example, we are changing the name of the column ‘id’ to ‘studentid’ from table ‘student_info’ as follows −

mysql> Select * from Student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
|  101 | YashPal | Amritsar   | History    |
|  105 | Gaurav  | Chandigarh | Literature |
|  130 | Ram     | Jhansi     | Computers  |
|  132 | Shyam   | Chandigarh | Economics  |
|  133 | Mohan   | Delhi      | Computers  |
+------+---------+------------+------------+
5 rows in set (0.02 sec)

mysql> ALTER TABLE Student_info CHANGE id studentid int;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> Select * from Student_info;
+-----------+---------+------------+------------+
| studentid | Name    | Address    | Subject    |
+-----------+---------+------------+------------+
|       101 | YashPal | Amritsar   | History    |
|       105 | Gaurav  | Chandigarh | Literature |
|       130 | Ram     | Jhansi     | Computers  |
|       132 | Shyam   | Chandigarh | Economics  |
|       133 | Mohan   | Delhi      | Computers  |
+-----------+---------+------------+------------+
5 rows in set (0.00 sec)

The above result set shows that the name of the column ‘id’ has been changed to ‘Studentid’.

raja
Published on 22-Feb-2018 13:05:30
Advertisements