database – Rename a table in MySQL

The Question :

296 people think this question is useful

Renaming a table is not working in MySQL

RENAME TABLE group TO member;

The error message is

#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 
        RENAME TO member' at line 1

The query is working fine on other tables for me, but not with the table group.

The Question Comments :
  • here’s the documentation
  • Both sqls are the same. Try this rename table ‘group’ to member
  • This illustrates nicely how pointless it is to use all-caps for keywords.
  • No, it does not. All-caps keywords improve readability and are expected standard style.

The Answer 1

490 people think this answer is useful

group is a keyword (part of GROUP BY) in MySQL, you need to surround it with backticks to show MySQL that you want it interpreted as a table name:

RENAME TABLE `group` TO `member`;

added(see comments)- Those are not single quotes.

The Answer 2

134 people think this answer is useful

Please try

RENAME TABLE  `oldTableName` TO  `newTableName`

The Answer 3

31 people think this answer is useful

The mysql query for rename table is

Rename Table old_name TO new_name

In your query, you’ve used group which one of the keywords in MySQL. Try to avoid mysql keywords for name while creating table, field name and so on.

The Answer 4

23 people think this answer is useful
ALTER TABLE old_table_name RENAME new_table_name;

or

RENAME TABLE old_table_name TO new_table_name;

The Answer 5

19 people think this answer is useful

Rename a table in MySQL :

ALTER TABLE current_name RENAME new_name;

The Answer 6

18 people think this answer is useful

Table name change

RENAME TABLE old_table_name TO new_table_name;

The Answer 7

14 people think this answer is useful

group – is a reserved word in MySQL, that’s why you see such error.

#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 
        RENAME TO member' at line 1

You need to wrap table name into backticks:

RENAME TABLE `group` TO `member`;

The Answer 8

13 people think this answer is useful
ALTER TABLE `group` RENAME `member`

group is keyword so you must have to enclose into group

The Answer 9

9 people think this answer is useful
RENAME TABLE tb1 TO tb2;

tb1 – current table name. tb2 – the name you want your table to be called.

The Answer 10

8 people think this answer is useful

According to mysql docs: “to rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead.”

So this is the most portable method:

ALTER TABLE `old_name` RENAME `new_name`;

The Answer 11

6 people think this answer is useful

Try any of these

RENAME TABLE `group` TO `member`;

or

ALTER TABLE `group` RENAME `member`;

The Answer 12

5 people think this answer is useful

For Mysql 5.6.18 use the following command

ALTER TABLE `old_table` RENAME TO `new_table`

Also if there is an error saying “…. near RENAME TO …” try removing the tick `

The Answer 13

2 people think this answer is useful

You can use

RENAME TABLE `group` TO `member`;

Use back tick (`) instead of single quote (‘).

The Answer 14

1 people think this answer is useful

Running The Alter Command

1.Click the SQL tab at the top.

2.In the text box enter the following command: ALTER TABLE exampletable RENAME TO new_table_name;

3.Click the go button.

source : https://my.bluehost.com/hosting/help/2158

The Answer 15

-1 people think this answer is useful

Without giving the database name the table is can’t be renamed in my case, I followed the below command to rename the table.

RENAME TABLE current_db.tbl_name TO current_db.tbl_name;

The Answer 16

-6 people think this answer is useful

Right Click on View > New Query

And Type: EXEC sp_rename ‘Table’, ‘NewName’

Then Click on Run button at the top left corner of the page.

Add a Comment