How to Rename a Table in MySQL

1. Using RENAME Statement to Rename a Table in MySQL
2. Using ALTER TABLE Statement to Rename a Table in MySQL

This tutorial will introduce how to rename a table in the MySQL database.

Generally, organizations have changes in their product requirements, so there is a constant need to change the name of tables and columns in a particular database. These changes would help reflect the updated information. MySQL assists us in doing this operation efficiently using multiple techniques.

To change the name of a particular table, we use the RENAME TABLE statement as follows.

RENAME TABLE old_table_name TO new_table_name;

We can rename a table in MySQL using the following two techniques.

• Using the RENAME TABLE method.
• Using the ALTER TABLE method.

Before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows.

-- 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");

Now let’s rename the student_details table to student_information table.

Using RENAME Statement to Rename a Table in MySQL

We can use the rename statement to get this done. This statement can be written as RENAME previous_table_name to new_name_to_be_assigned; in MySQL. In order to perform the operation mentioned above on the student_details table, we can use the following query.

RENAME TABLE student_details TO student_information;

Now, let us check the student_information table with the following query.

SELECT * from student_information;

The query mentioned above will give us 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

It shows that the table has been successfully renamed without any altercations in the table data.

Using ALTER TABLE Statement to Rename a Table in MySQL

We can also use the ALTER TABLE statement in MySQL to rename a table. The syntax for this operation can be illustrated as follows.

ALTER TABLE previous_table_name RENAME new_name_to_be_assigned;

In order to rename the student_details table using the ALTER TABLE technique, we can use the following query.

ALTER TABLE student_details RENAME student_information;

Now, let us check the student_information table with the following query.

SELECT * from student_information;

The query mentioned above will give us 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