MySQL RENAME TABLE Statement

MySQL provides us multiple options to do the same operation but in slightly different ways. The MySQL RENAME TABLE is an alternative option we can use to rename the tables as well to perform other operations. In this tutorial, we will learn about the RENAME TABLE statement and its uses. We will start with the introduction of the statement, and then we will see its syntax. Also, we will understand this topic via some practical examples. Let’s get started!

Introduction to MySQL RENAME TABLE Statement

MySQL RENAME TABLE statement is an alternative option for renaming the table.

However, you can rename only a single table using the ALTER TABLE statement. Whereas, using the RENAME TABLE statement, you can rename multiple tables in one statement. Isn’t it amazing!

You can use the functionality of RENAME TABLE statement of renaming multiple tables for swapping the names of two tables.

Moreover, the RENAME TABLE statement is used to move the table from one database to another database without writing a second statement.

For using the RENAME TABLE statement, you must have CREATE, INSERT, ALTER and RENAME privileges.
Now, let’s see the syntax of the MySQL RENAME TABLE statement.

Syntax of MySQL RENAME TABLE Statement

The syntax of the RENAME TABLE statement is similar to the ALTER TABLE statement. Look at the following syntax to rename a single table using the RENAME TABLE statement.

RENAME TABLE old_name TO new_name;Code language: SQL (Structured Query Language) (sql)

The above syntax is equivalent to the following syntax-

ALTER TABLE old_name TO new_name;Code language: SQL (Structured Query Language) (sql)

The best part of the RENAME TABLE statements is that you can rename multiple tables in one statement. Following is the syntax to rename multiple tables.

RENAME TABLE 
old_name1 TO new_name1,
old_name2 TO new_name2,
old_name3 TO new_name3;Code language: SQL (Structured Query Language) (sql)

The above syntax can be used to swap the names of two tables.

RENAME TABLE 
old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;Code language: SQL (Structured Query Language) (sql)

The following syntax will swap two table names using the third table name, which acts as a temporary variable. Note that no table should be present in the database with the temporary name.

The RENAME TABLE statement is also used to move the table from one database to another. Following is the syntax to move the table to another database.

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;Code language: SQL (Structured Query Language) (sql)

Note that the table of name “tbl_name” must not be present in the destination database.

Examples of MySQL RENAME TABLE Statement

We will use two tables of the following description and data to demonstrate the examples.

1. Rename Single Table Using RENAME TABLE

Now, we will rename table s1 to table1 and s2 to table2 using two different queries.

RENAME TABLE s1 TO table1;
RENAME TABLE s2 TO table2;Code language: SQL (Structured Query Language) (sql)

The rename is successful. We can now use the new names. If we try to display the data by old table names, it will show an error.

SELECT * FROM s1;Code language: SQL (Structured Query Language) (sql)

2. Rename Multiple Tables Using RENAME TABLE

Now we will rename the same two tables in the single query. Here, we will assign the previous names of the table, i.e. s1 and s2.

RENAME TABLE
table1 TO s1,
table2 TO s2;Code language: SQL (Structured Query Language) (sql)

The tables are renamed with the new names, and you can use the new names for doing operations on them.

3. Move Table to Another Database Using RENAME TABLE

This is an add-on feature of the RENAME TABLE over ALTER TABLE, using which we can move the table from one database to another database using a single statement.

Before performing the move operation, the only condition is that the destination database must not contain the table of the same name as the current table.

Let’s now see an example of it.

RENAME TABLE journaldev.s1 TO student.s1;Code language: SQL (Structured Query Language) (sql)

Here, table “s1” has been moved from the “journaldev” database to the “student” database.

Summary

In this tutorial, we learned-

• What is the RENAME TABLE statement?
• What the RENAME TABLE is used for?
• How to rename a single table?
• How to rename multiple tables in one query?
• How to move the table to a new database?

I hope you have understood the topic very well. You may try to utilize the function in your projects and use it. For more such fantastic tutorials, do visit mysql.com.

References

MySQL official documentation on the RENAME TABLE statement.