Sometimes you may need to change a table name in MySQL. 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.
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.
Here is an example to rename sales table to orders using ALTER TABLE statement.
alter table sales rename to orders;
Also read : How to run multiple MySQL instances on same machine
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 sales table to orders using RENAME TABLE statement.
rename table sales to orders;
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
Here are the key differences between ALTER TABLE and RENAME TABLE statements.
1. Unlike ALTER TABLE, you can use RENAME TABLE to rename multiple tables at once. Here’s the syntax for renaming multiple tables using RENAME statement.
RENAME TABLE old_table1 TO new_table1,old_table2 TO new_table2,old_table3 TO new_table3;
2. RENAME TABLE cannot be used to rename temporary tables. ALTER TABLE works with temporary tables.
3. RENAME TABLE works for views also. ALTER TABLE does not support renaming views.
As you can see, it is quite easy to change table name 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!
