How to rename table in MySQL

Renaming means changing the name of table after it has been created.
Since this is a basic operation for a database, all the database have a standard syntax for renaming tables but MySQL provides two variants of syntax to rename tables.

Method 1 : Using ALTER query
The syntax of using ALTER query for renaming table is

ALTER TABLE <existing table name> RENAME TO <new table name>;

Thus if existing table name is codippa and I want it to change to codippanew, the query would be

ALTER TABLE `codippa` RENAME TO `codippanew`;

Changing table name is just one of the usages of ALTER query.
It is used for many other purposes as well, such as for adding/removing/renaming a column, for adding/removing/renaming a constraint, changing a column data type etc.

Method 2 : Using RENAME query
This is a MySQL specific syntax and is not guaranteed to run on other database systems. Syntax is

RENAME table <existing table name> TO <new table name>;

Thus if existing table name is codippa and I want it to change to codippanew, the query would be

RENAME TABLE `codippa` TO `codippanew`;

More than one tables can be renamed using this query using the syntax

RENAME TABLE <oldtable1> TO <newtable1>, <oldtable2> TO <newtable1>;

Let’s tweak in

  1. It is not mandatory to enclose table names in ` ` but if a table name is a reserved word then it should be enclosed between ` and `.
  2. Select the database(in which the tables reside) before executing the queries or else prefix the database name and a .(dot) before table names as [database].[tablename].
  3. If you are facing the below error while renaming a table,

    Error Code: 1064. 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 ‘group’ at line 1

    Chances are that you are using the word “group” in the query either renaming table “group” to some other name or renaming a name to “group”.
    To remove this error just surround the word group with ` `(backticks).
    This is because group is a keyword in MySQL (group by).

Leave a Reply