How to rename MySQL table tutorial

To rename an existing MySQL table in your database, you can use either the RENAME TABLE statement or the ALTER TABLE statement.

The syntax for renaming a table the ALTER TABLE statement is as follows:

ALTER TABLE [old_table_name] RENAME [new_table_name];

You need to replace [old_table_name] with an existing table name and [new_table_name] with the new name that you desire.

When using the RENAME TABLE statement, the syntax is as follows:

RENAME TABLE [old_table_name] TO [new_table_name]

The difference between ALTER TABLE and RENAME TABLE is that the RENAME TABLE statement allows you to rename multiple tables in one statement.

Take a look at the example below of how RENAME TABLE can rename multiple tables at once:

RENAME TABLE [old_table_name] TO [new_table_name],
  [old_table_name] TO [new_table_name],
  [old_table_name] TO [new_table_name],
  [old_table_name] TO [new_table_name];

The ALTER TABLE statement can only change one table name at a time:

ALTER TABLE [old_table_name] RENAME [new_table_name];
ALTER TABLE [old_table_name] RENAME [new_table_name];
ALTER TABLE [old_table_name] RENAME [new_table_name];

Note that you need the ALTER, DROP, CREATE, and INSERT privileges for all the tables that you want to rename.

Here’s an example of how to grant those privileges to your user:

GRANT ALTER, DROP, CREATE, INSERT ON school_db TO 'nathan'@'localhost';

The above statement will grant the privileges to user nathan for the school_db database only. You need to change the database and user names above to your actual names.

Finally, if you encounter an ERROR 1064 when renaming your table, you may have a table name that’s reserved as a MySQL keyword.

For example, I have a table named distinct that I want to rename, but the following statement causes an error:

mysql> RENAME TABLE distinct TO users;
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 'distinct TO users' at line 1

The error above is because MySQL thought I want to execute the DISTINCT clause.

To fix the error, and avoid colliding with MySQL reserved keywords, the table name must be enclosed in backticks as shown below:

RENAME TABLE `distinct` TO users;

It’s a good practice to always enclose table names with backticks in your SQL statements, so I recommend you always do it when you write table names in your MySQL statements.

RENAME TABLE `distinct` TO `users`;

And that’s how you can rename tables in your MySQL database server. Nice work! 😉