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).

0

Share your thoughts !!

Close Menu

Never Miss an article !

Get the new post delivered straight into your inbox, enter your email and hit the button

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

codippa will use the information you provide on this form to be in touch with you and to provide updates and marketing.