MySQL RENAME TABLE Statement

In MySQL, the RENAME TABLE statement is used to rename one or more tables in a database. We will discuss about MySQL RENAME TABLE statement. We will see how to rename one table, multiple tables, and a temporary table. We will also see the different ways we have to rename a table.

Sometimes our table 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.

We have to make sure that the new name that we are giving to the table does not exist in our database, then only we can give that name to any table and the table to which we are going to give a new name must exist in our database. Else, it will give an error message. MySQL RENAME TABLE statement can be used to change the table name.

RENAME TABLE Statement

Efficient database management often involves modifying table structures to meet evolving requirements. In MySQL, the RENAME TABLE statement emerges as a powerful tool, allowing database administrators to seamlessly rename one or more tables.

Syntax:

RENAME TABLE old_table_name TO new_table_name [, …];

RENAME TABLE Examples

Lets see the example how to rename the table. We need to create the database first. We can create a database using the following command:

CREATE DATABASE GFG;

After creating we need to select that database. This can be done by using the following command:

USE GFG;

Now, create a table named as old_table using the following command and insert some data in the table

CREATE TABLE old_table (
     id INT PRIMARY KEY,
     name VARCHAR(50)
      );

INSERT INTO old_table (id, name) VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob');

Now Rename the Table ‘old_table’ to ‘new_table’. For renaming a table RENAME TABLE statement is used.

RENAME TABLE old_table TO new_table;

For checking that the old_table is renamed to new_table query all rows and columns using SELECT statement.

SELECT * FROM new_table;

Output:

new_table

RENAME Multiple Tables

To rename multiple tables MySQL RENAME TABLE statement can be used.

1. Create Two Tables

CREATE TABLE old_table1 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE old_table2 (
    id INT PRIMARY KEY,
    description TEXT
);

2. Insert Data in Tables

Insert some data in both the tables using the following commands:

INSERT INTO old_table1 (id, name) VALUES (1, 'John'), (2, 'Alice');

INSERT INTO old_table2 (id, description) VALUES (1, 'Description 1'), (2, 'Description 2');

3. Rename the Tables

Now, rename both tables simultaneously to ‘new_table1’ and ‘new_table2’. You have to give a comma separated commands in q SQL query as shown in the following example:

RENAME TABLE old_table1 TO new_table1, old_table2 TO new_table2;

4. Check Whether Tables are Renamed or Not

To check that the tables are renamed or not , use SELECT statement and query all rows and columns in both the tables.

Use the following commands:

SELECT * FROM new_table1;

SELECT * FROM new_table2;

Output:

Data of new_table1

Output:

Data of new_table2

ALTER Statement

In MySQL, we can also use the ALTER TABLE statement to rename the existing table in the current database. The following example will help you to understand it better:

Create the table and insert data into them using the following commands:

CREATE TABLE old_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO old_table (id, name) VALUES (1, 'John'), (2, 'Alice');

Now use ‘ALTER TABLE’ statement with the ‘RENAME‘ option and change the table name.

Use the following command to rename the table using the ‘ALTER’ statement:

ALTER TABLE old_table RENAME TO new_table;

Use SELECT Statement to check whether the table is renamed or not.

SELECT * FROM new_table;

Output:

Data of new_table

RENAME Temporary Table

In MySQL, temporary tables are tables that allows us to keep temporary data, which is visible and accessible in the current session.

1. Create a Temporary table

Create a temporary table using the following command:

CREATE TEMPORARY TABLE temp_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

2. Insert data

Now, insert data in temporary table using the following command:

INSERT INTO temp_table (id, name) VALUES (1, 'John'), (2, 'Alice');

3. Rename Table

To rename temporary tables , we cannot use RENAME TABLE statement, we need to use ALTER TABLE statement.

Use the following command for rename the temporary table:

ALTER  TABLE temp_table  RENAME TO new_temp_table;

4. Check whether the Table is Renamed or Not

To check the table is renamed or not, you can use a SELECT statement.

Use the following command:

SELECT * FROM new_temp_table;

Output:

Data of new_temp_table

Conclusion

In Conclusion ,we discussed about how to rename a table, and also how to rename multiple tables. And how to rename a temporary table. RENAME TABLE and ALTER statements are used to rename tables. It’s a quick and efficient way to update table names without messing with the data or structure. You can easily rename one or more tables in one go, making it a handy tool for managing your database. It ensures a smooth transition without any disruption to the data.