Summary: in this tutorial, you will learn how to rename an existing table in MySQL using MySQL
RENAME TABLE statement and
ALTER TABLE statement.
Sometimes we need to give a more meaningful name to a table in the database to reflect the data it holds. MySQL allows us to change the name of one or more tables using the MySQL
RENAME TABLE statement.
MySQL RENAME TABLE Syntax
The syntax for renaming a table using the
RENAME TABLE statement is as follows.
RENAME TBALE old_table_name TO new_table_name;
Here, we need to ensure that the
new_table_name must not exist in the same schema or database and the
old_table_name must exist in the database. Otherwise, MySQL will throw an error.
Also, we need to ensure that the table we are renaming is not locked and no active transaction is active on this table.
MySQL also allows us to rename multiple tables in a single statement. Following is the syntax of changing names for multiple tables.
RENAME TABLE old_table_name1 TO new_table_name1, old_table_name2 TO new_table_name2, old_table_name3 TO new_table_name3;
RENAME TABLEstatement to change the name of a temporary table. However, you can use the
ALTER TABLEstatement to rename a temporary table.
From the MySQL version 8.0.13, it is suggested that you should use the WRITE LOCK before issuing the
RENAME TABLE statement for changing the name of a table. Basically, it ensures that no other session acquires any lock on that table for performing any task until the rename table completes. For example, the following is a valid statement.
LOCK TABLE old_tab_name1 WRITE; RENAME TABLE old_tab_name1 TO new_tab_name1, new_tab_name1 TO new_tab_name2;
MySQL RENAME TABLE Example
Let us now understand the concept of the rename table using various examples.
Rename a Single Table
In this example, we will see how to change the name of a table in MySQL. Suppose, we have a table named
students. For some reason, we want to change the name of the table to
student_details. You use the following statement to change the name of the table.
mysql> RENAME TABLE students To student_details;
We will see the name of the
students table changed to
Rename Multiple Tables
This example shows you how to change the name of multiple tables using a single
RENAME TABLE statement. Consider, we have two tables:
teacher_details. We want to change the name of table
students and table
teachers. Following is the statement.
mysql> RENAME TABLE student_details to students, -> teacher_details to teachers;
You can see that the name of the table
student_details is changed to
students and the name of the table
teacher_details is changed to
Rename Table using ALTER TABLE
You can also use the
ALTER TABLE statement to rename a table.
ALTER TABLE old_table_name RENAME TO new_table_name;
In the following example, we will change the name of the
students table back to
student_details using the
ALTER TABLE statement.
mysql> ALTER TABLE students RENAME TO student_details; Query OK, 0 rows affected (0.06 sec)
You can observe that the name of the table again changed to
Rename a Temporary Table
A temporary table is temporary in nature that means the table is visible and accessible in the current session only. We generally use this kind of table for intermediate calculation. Once the current session ends, the table structure along with the data removed from the database.
To rename a temporary table, you use the
ALTER TABLE statement. You cannot use the
RENAME TABLE statement to change the name of a temporary table in MySQL.
First, we can a temporary table using the following statement.
mysql> CREATE TEMPORARY TABLE Student_marks( -> roll_no INT PRIMARY KEY NOT NULL, -> name varchar(100) NOT NULL, -> total_marks DECIMAL(10,2) NOT NULL DEFAULT 0.00, -> total_subjects INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.01 sec)
Second, insert some data into the table.
mysql> INSERT INTO Student_marks (roll_no,name,total_marks,total_subjects) VALUES -> (009,'Sagar Sharma',800,10), -> (015,'Mrinal Sarkar',750,10), -> (018,'Rahul Bose',780,10); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0
Third, verify the data using the
mysql> SELECT * FROM Student_marks;
Now, run the following command to rename the temporary table.
mysql> RENAME TABLE Student_marks To Student_scores;
This will give the following error.
ERROR 1146 (42S02): Table 'university.student_marks' doesn't exist
Finally, you can use the
ALTER TABLE statement to rename the temporary table.
mysql> ALTER TABLE Student_marks RENAME TO Student_scores; Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0
Visual representation of the above steps.