How To Rename Table in MySQL

Last updated on July 26th, 2024 at 07:48 am

Sometimes database administrators may need to change a table name in MySQL during database restructuring, or to make it look more intuitive. In this article we will look at how to rename table in MySQL. There are two ways to rename table in MySQL – using ALTER and using RENAME statements. We will look at both these approaches and understand their differences.

How To Rename Table in MySQL

You can rename table in MySQL using ALTER TABLE or RENAME TABLE statement. We will learn how to use them to change table names in MySQL. We will also look at couple of important use cases.

Rename Table with ALTER Table Statement

Here is the syntax to rename table using ALTER TABLE statement.

alter table old_table_name
rename to new_table_name;

In the above statement, you need to specify the old table name and new table name.

Let us say you have the following sales table (id, amount, order_date).

mysql> describe sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Here is an example to rename sales table to orders using ALTER TABLE statement.

alter table sales
rename to orders;

Now if you try to view details of sales table, you will get an error since it does not exist.

mysql> describe sales;
ERROR 1146 (42S02): Table 'sample.sales' doesn't exist

Instead, if you try to view details of its new name orders, then you will get the right output.

mysql> describe orders;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Also read : How to run multiple MySQL instances on same machine

Rename Table Using RENAME Table Statement

Here is the syntax to rename table using RENAME statement.

rename table old_table_name to new_table_name;

In the above statement, you need to specify the old table name and new table name.

Here is an example to rename orders table to sales using RENAME TABLE statement.

mysql> rename table orders to sales;

Now if you try to get details of orders table, it will show an error.

mysql> describe orders;
ERROR 1146 (42S02): Table 'sample.orders' doesn't exist

On the other hand, if you try to view details of new table name, it will give desired result.

mysql> describe sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Please note:

When you rename a table in MySQL, all views, stored procedures and foreign keys that reference the old table name will stop working. So you will need to update these views, stored procedures and foreign keys to reference its new table name.

Also read : How to Insert multiple rows in MySQL

Rename Multiple Tables

Often you may need to rename multiple tables at one go. In such cases, you need to use RENAME TABLE statement. Using ALTER TABLE statement will give you an error. Here is the syntax to use RENAME TABLE with multiple tables. This is a major difference between RENAME TABLE and ALTER TABLE.

mysql> RENAME TABLE table1 TO new_table1, table2 TO new_table2; 

Here is the query to rename sales to orders and employees to customers.

mysql> RENAME TABLE sales TO orders, employees TO customers; 

If you use ALTER table statement, it will give an error as shown below.

mysql> alter table sales rename to orders. employees rename to customers;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rename to customers' at line 1

Rename Temporary Table

On the other hand, ALTER TABLE can be used to rename temporary table but RENAME TABLE cannot be used for this purpose. Here is an example.

mysql> create temporary table sales_1(id int, amount int, order_date date);

mysql> insert into sales_1(id, amount, order_date) 
       values(1, 250, '2024-07-26'),
             (2,450,'2024-07-27'),
             (3,200,'2024-05-12');

mysql> select * from sales_1;
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |    250 | 2024-07-26 |
|    2 |    450 | 2024-07-27 |
|    3 |    200 | 2024-05-12 |
+------+--------+------------+

If you use RENAME statement to rename this table, you will get an error.

mysql> rename table sales_1 to sales_2;
ERROR 1017 (HY000): Can't find file: '.\sample\sales_1.frm' (errno: 2)

Therefore, you need to use ALTER TABLE statement.

mysql> alter table sales_1 rename to sales_2;

mysql> select * from sales_2;
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |    250 | 2024-07-26 |
|    2 |    450 | 2024-07-27 |
|    3 |    200 | 2024-05-12 |
+------+--------+------------+

Difference between ALTER TABLE and RENAME TABLE

Here are the key differences between ALTER TABLE and RENAME TABLE statements.

ALTER TABLE	RENAME TABLE
Can be used to rename only one table at a time. Cannot be used to rename multiple tables.	Can be used to rename single or multiple at once
Can be used to rename temporary tables	Cannot be used to rename temporary tables
Can be used to rename views also	Cannot be used to rename views.

Conclusion

As you can see, it is quite easy to change table name in MySQL. In this article, we have learnt how to rename tables in MySQL using ALTER TABLE and RENAME TABLE statements. We have also learnt how to rename multiple tables using RENAME statement. We have learnt how to rename temporary tables with ALTER TABLE statement. Depending on your requirement, you can use either of these statements to change table names in MySQL.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!