How to Rename a Column in MySQL Database

1. Renaming the Column Using the RENAME Statement
2. Renaming the Column Using the CHANGE Statement

In this tutorial, we aim at exploring different methods to rename a column in MySQL.

The ALTER TABLE command is primarily used to change the format of a given MySQL table. It can be used to add columns, change data type within columns, delete columns, and sometimes even rename the entire database.

There are two main ways to change the column name with the ALTER TABLE command.

• RENAME statement with ALTER TABLE command
• CHANGE statement with ALTER TABLE command

However, before we begin, we need to create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. To view the entries in the data, we use the following code.

SELECT * FROM student_details;

The line of code above would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Let’s aim at renaming the stu_firstName column to simply firstName.

Renaming the Column Using the RENAME Statement

One of the most basic methods to change the name of a column in MySQL is to use the RENAME COLUMN clause. The syntax to perform the above operation can be stated as follows:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Here, the old_name represents the column name to be changed, and the new_name represents the new name of the column.

To change the stu_firstName column, we will use the following code:

ALTER TABLE student_details RENAME COLUMN stu_firstName TO firstName;

The line of code above would give the following output:

stu_id    firstName 	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Renaming the Column Using the CHANGE Statement

One of the more intricate methods to change the name of a column is by using the CHANGE clause. This clause also helps in adjusting the data type of the column along with the column name. The syntax to perform this operation is as follows:

ALTER TABLE table_name CHANGE old_name new_name Data Type;

To change the stu_firstName column along with its data type to VARCHAR(40), we will use the following code:

ALTER TABLE student_details CHANGE stu_firstName firstName VARCHAR(40);
-- Here VARCHAR(40) is the new data type

The code snippet above would give the following output with the new data type as mentioned above.

stu_id    firstName 	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Thus, with the help of the above two techniques, we can efficiently change the name of any column of a table in MySQL. We can conclude that understanding the ALTER TABLE command is a prerequisite for exploring complex statements.