MySQL RENAME TABLE

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;

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;

Output:

We will see the name of the students table changed to student_details.

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: student_details and teacher_details. We want to change the name of table student_details to students and table teacher_details to teachers. Following is the statement.

mysql> RENAME TABLE student_details to students,
    -> teacher_details to teachers;

Output: 

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 teachers;

Rename Table using ALTER TABLE

You can also use the ALTER TABLE statement to rename a table.

Syntax:

ALTER TABLE old_table_name RENAME TO new_table_name;

Example:

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)

Output:

You can observe that the name of the table again changed to student_details from students.

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 SELECT statement.

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.