How to rename a column in MySQL

In this article, we will see how to rename a column in a MySQL table.

Table of Contents

• MySQL: Rename the column
• MySQL change column name without losing the data

Let us get started by making the sample data. We will be creating a table named student_enroll_data.

CREATE TABLE student_enroll_data (
student_id INT,
student_name VARCHAR(50),
enroll_date DATE,
student_ssn_no INT,
fee_submitted DECIMAL(10,2)
);

Action Output:-

As we can see in image_1, table is created successfully.

MySQL: Rename the column

In this section, we will use RENAME and CHANGE clauses with ALTER statement to rename the column. Let us move ahead with RENAME first.

Syntax of RENAME in MySQL

ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName;

Example of RENAME in MySQL

In the below query, we are renaming the column fee_submitted to annual_fee_submitted in table student_enroll_data.

ALTER TABLE student_enroll_data RENAME COLUMN fee_submitted TO annual_fee_submitted;

Verify the same by executing:

SELECT 
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'student_enroll_data';

Output:-

Output in image_2 shows that the column’s name is changed to annual_fee_submitted.

Syntax of CHANGE in MySQL

ALTER TABLE tableName CHANGE COLUMN oldColumnName newColumnName datatype[size];

Example of CHANGE in MySQL

In the below query, we are changing the name of column annual_fee_submitted back to fee_submitted.

ALTER TABLE student_enroll_data CHANGE annual_fee_submitted fee_submitted DECIMAL(10,2);

Verify the same by executing:

SELECT 
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'student_enroll_data';

Output:-

Output in image_3 shows that the column’s name is changed back to fee_submitted from annual_fee_submitted.

MySQL change column name without losing the data

This section will see how to change the column name without losing the data already present in the column. Using the RENAME clause column will not lose the existing data of the column. Let us verify the same by executing the below queries.

• Step1: We will insert some data to table student_enroll_data.
• Step2: Rename the column fee_submitted.
• Step3: Verify the data in the column.

#insert rows into student_enroll_data
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(1,"Daniel",'2021-12-12',1147483782,12378.90),
(2,"Sandy",'2021-10-12',1147483788,14578.90),
(3,"Ryma",'2021-11-22',1147483789,22378.90),
(4,"Jennifer",'2021-12-02',1147483790,12378.90),
(5,"Austin",'2021-11-12',1147483791,12378.90),
(6,"George",'2021-10-10',1147483792,12788.90),
(7,"Veronica",'2021-02-13',1147483793,12378.90);
# see what got inserted into the table student_enroll_data
SELECT * FROM student_enroll_data;

Output:-

We are renaming the column from fee_submitted to annual_fee_submitted.

ALTER TABLE student_enroll_data RENAME COLUMN fee_submitted TO annual_fee_submitted;

Let us see the changes made to the table by executing:

SELECT * FROM student_enroll_data;

Output:-

Output in image_5 shows that the column name is changed to annual_fee_submitted, and data got preserved as well.

We hope this article helped you with the renaming of columns in a MySQL table. Good Luck!!!.