MySQL Rename Table

Sometimes our table name is non-meaningful, so it is required to rename or change the name of the table. MySQL provides a useful syntax that can rename one or more tables in the current database.

Syntax

The following are the syntax used to change the name of the table:

mysql> RENAME old_table TO new_table

Here, we have to make sure that new_table_name must not exist, and old_table_name should be present in the database. Otherwise, it will throw an error message. It is to ensure that the table is not locked as well as there are no active transactions before executing this statement.

NOTE: If we use the RENAME TABLE statement, it is required to have ALTER and DROP TABLE privileges to the existing table. Also, this statement cannot change the name of a temporary table.

We can also use the MySQL RENAME TABLE statement to change more than one table name with a single statement, as shown below:

RENAME TABLE old_tab1 TO new_tab1,  

             old_tab2 TO new_tab2, old_tab3 TO new_tab3;

  

From the MySQL 8.0.13 version, we can change the old table name locked with a LOCK statement and also uses the WRITE LOCK clause. For example, following are the valid statement:

mysql> LOCK TABLE old_tab_name1 WRITE;  

RENAME TABLE old_tab_name1 TO new_tab_name1,  

             new_tab_name1 TO new_tab_name2;

Following statement are not permitted:

mysql> LOCK TABLE old_tab_name1 READ;  

RENAME TABLE old_tab_name1 TO new_tab_name1,  

             new_tab_name1 TO new_tab_name2;

Before MySQL 8.0.13 version, we cannot change the table name that was locked with the LOCK TABLE statement.

MySQL also use the RENAME TABLE statement for moving a table from one database to other database, which is show below:

mysql> RENAME TABLE current_db.tablel_name TO other_db.tablel_name

MySQL RENAME TABLE Example

Let us understand how the RENAME TABLE statement works in MySQL through the various examples. Suppose we have a table named EMPLOYEE, and due to some reason, there is a need to change it into the table named CUSTOMER.

Table Name: employee

Next, execute the following syntax to change the table name:

mysql> RENAME employee TO customer; 

Output

We will see that the table named “employee” will be changed into a new table name “customer”:

In the above output, we can see that if we use the table name employee after executing a RENAME TABLE statement, it will throw an error message.

How to RENAME Multiple Tables

RENAME TABLE statement in MySQL also allows us to change more than one table name within a single statement. See the below statement:

Suppose our database “myemployeedb” having the following tables:

If we want to change the table name customer into employee and table name shirts into garments, execute the following statement:

mysql> RENAME TABLE customer TO employee, shirts TO garments;  

Output

We can see that the table name customer into employee and table name shirts into garments have successfully renamed.

Rename table using ALTER statement

The ALTER TABLE statement can also be used to rename the existing table in the current database. The following are the syntax of the ALTER TABLE statement:

ALTER TABLE old_table_name RENAME TO new_table_name;  

See the following query that changes the existing table name garments into new table name shirts:

mysql> ALTER TABLE garments RENAME TO shirts:  

Output

Here, we can see that the table name garments renamed into table name shirts.

How to RENAME Temporary Table

A temporary table allows us to keep temporary data, which is visible and accessible in the current session only. So, first, we need to create a temporary table using the following statement:

mysql> CREATE TEMPORARY TABLE Students( name VARCHAR(40) NOT NULL, total_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subjects INT UNSIGNED NOT NULL DEFAULT 0);   

Next, insert values into this table:

mysql> INSERT INTO Students(name, total_marks, total_subjects) VALUES ('Joseph', 150.75, 2), ('Peter', 180.75, 2)

 

Next, run the show table command to check the temporary table:

mysql> RENAME TABLE Students TO student_info

Now, run the following command to change the name of the temporary table:

mysql> RENAME TABLE Students TO student_info

It will throw an error message, as shown below:

Thus, MySQL allows ALTER table statement to rename the temporary table:

mysql> ALTER TABLE Students RENAME TO student_info