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:

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:

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:

Following statement are not permitted:

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 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:

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:

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:

See the following query that changes the existing table name garments into new table name 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:

Next, insert values into this table:

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

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

It will throw an error message, as shown below:

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

Output