MySQL RENAME TABLE Statement

The MySQL RENAME TABLE statement is a Data Definition Language (DDL) command that allows you to change the name of an existing table in a database. It provides a fast and efficient way to rename one or more existing tables without affecting the data stored in them.

For example, suppose your database contains a table named student. Later, you decide that students is a more appropriate table name. Instead of creating a new table and copying the data, you can simply rename the table using the MySQL RENAME TABLE statement.

After renaming the table, the following remain unchanged:

All existing data in the table.
The table structure.
Indexes.
Constraints.
Column definitions and data types.

Only the table name changes.

Syntax of MySQL RENAME TABLE

The general syntax of the RENAME TABLE statement in MySQL is:

RENAME TABLE old_table_name
TO new_table_name;

In this syntax:

RENAME TABLE – Specifies that you want to rename an existing table.
old_table_name – Specifies the current name of the table.
TO – Separates the current table name from the new table name.
new_table_name – Specifies the new name for the table.

How Does the RENAME TABLE Statement Work?

The RENAME TABLE statement performs the following actions:

Finds the existing table.
Changes the table name to the new name.
Keeps all existing rows unchanged.
Preserves the table structure.
Preserves all indexes.
Preserves all constraints.
Preserves column definitions and data types.
Updates the table name in the database metadata.

Rules of MySQL RENAME TABLE

You should keep the following rules in mind before renaming a table.

The old table must already exist. If the table does not exist, MySQL returns an error.
The new table name must not already exist. If the table already exits, MySQL cannot rename the table because the destination table already exists.
You must have the required privileges. If you do not have permission to rename a table, MySQL returns a permission error.
You should use meaningful and descriptive table names.

Example of RENAME TABLE Statement in MySQL

Create a Table in MySQL

Let’s create a table in MySQL that we will use throughout this tutorial.

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    course VARCHAR(50)
);

Insert Records in Table

INSERT INTO student
VALUES
(101, 'John', 'Java'),
(102, 'Emma', 'Python'),
(103, 'David', 'MySQL');

Current Table Structure

student_id	student_name	course
101	John	Java
102	Emma	Python
103	David	MySQL

Example 1: Rename a Table

To rename the student table to students, type the following SQL query:

RENAME TABLE student
TO students;

In this example, MySQL changes the table name from student to students. The data in the table remains exactly the same. Now the table name in the database becomes students.

Verify the Renamed Table

To verify that the table has been renamed, execute the following SQL query:

SHOW TABLES;

Output:

students

Renaming Multiple Tables in MySQL

MySQL allows you to rename multiple tables with a single RENAME TABLE statement. Instead of executing separate RENAME TABLE statements for each table, you can rename all required tables with one command.

This feature is useful when you need to rename several tables at the same time, such as during database maintenance, schema reorganization, or when following a new naming convention.

The general syntax to rename multiple tables in MySQL is:

RENAME TABLE
old_table_name1 TO new_table_name1,
old_table_name2 TO new_table_name2,
old_table_name3 TO new_table_name3;

In this syntax:

Each old table name is followed by the TO keyword and its corresponding new table name.
Each rename operation is separated with a comma (,).
If the SQL query executes successfully, all specified tables are renamed.
Only the table names change. The data, table structure, indexes, constraints, and column definitions remain unchanged.

Example 2: Rename Multiple Tables

Let’s first create two tables named customer and product.

CREATE TABLE customer (
    customer_id INT
);
CREATE TABLE product (
    product_id INT
);

Next, we use a single RENAME TABLE statement to rename both tables.

RENAME TABLE
customer TO customers,
product TO products;

When MySQL executes this statement:

The customer table is renamed to customers.
The product table is renamed to products.

After the statement executes successfully, the table names become:

customer → customers
product → products

Only the table names change. All existing data, the table structure, indexes, constraints, and column definitions remain unchanged.
Thus, you can use a single MySQL RENAME TABLE statement to rename multiple tables at once instead of executing separate RENAME TABLE statements for each table.

Swap Two Table Names

Suppose we have a database containing the following two tables:

employee
employee_backup

You can swap their names by using a temporary table name.

RENAME TABLE
employee TO temp_table,
employee_backup TO employee,
temp_table TO employee_backup;

In this example, MySQL swaps the names of the employee and employee_backup tables by using a temporary table named temp_table. The statement performs the following operations in order:

Renames the employee table to temp_table.
Renames the employee_backup table to employee.
Renames the temp_table table to employee_backup.

After the statement executes successfully, the table names become:

employee — contains the data from employee_backup.
employee_backup — contains the data from employee.

Only the table names change. The data, table structure, indexes, constraints, and column definitions remain unchanged. This technique is useful when you want to replace an old table with a backup table.

Rename a Table in Another Database

Suppose you have the following two databases:

school_db
college_db

You can move the student table from the school_db database to the college_db database by using the MySQL RENAME TABLE statement.

RENAME TABLE
school_db.student
TO
college_db.student;

In this example, MySQL moves the student table from the school_db database to the college_db database.

school_db.student specifies the current location of the table.
college_db.student specifies the new location of the table.
All existing data, the table structure, indexes, constraints, and column definitions remain unchanged.
Only the location of the table changes from one database to another.

After the statement executes successfully:

Before:
school_db.student

After:
college_db.student

Using ALTER TABLE to Rename a Table

MySQL also allows you to rename an existing table by using the ALTER TABLE statement. The basic syntax to rename an existing table using the ALTER TABLE statement is:

ALTER TABLE student
RENAME TO students;

In this example, the ALTER TABLE statement changes the name of the student table to students.

The ALTER TABLE … RENAME TO statement and the RENAME TABLE statement both rename tables successfully. However, many developers prefer the RENAME TABLE statement because it supports renaming multiple tables in a single statement. The ALTER TABLE statement can rename only one table at a time.