Rename a table in MySQL

Question

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.

[here's the documentation](http://dev.mysql.com/doc/refman/5.0/en/rename-table.html) — Jason, Sep 29 '12 at 06:04
Both sqls are the same. Try this **rename table 'group' to member** — user1406062, Sep 29 '12 at 06:05
This illustrates nicely how pointless it is to use all-caps for keywords. — Martin Jambon, Sep 25 '14 at 17:56
No, it does not. All-caps keywords improve readability and are expected standard style. — fmalina, Nov 13 '15 at 14:36

score 568 · Answer 1 · edited Oct 15 '17 at 21:38

568

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.

edited Oct 15 '17 at 21:38

barlop

12,887
8
80
109

answered Sep 29 '12 at 06:06

Joachim Isaksson

176,943
25
281
294

62

And yes you need the back tick ` and not the single quote ' I did the single quote from habit, and got the error, but maybe this will save someone else 10 seconds – Paul Feb 03 '14 at 18:05
Some keywords, such as NAME are still available without back ticks. I think this availability it is a bad practice, which should be removed one day from MySQL – ad4s Oct 19 '16 at 16:13
3

Or don't use keywords for tables or columns. – wheeleruniverse Sep 22 '18 at 01:29
2

DO NOT USE KEYWORDS FOR TABLES OR COLUMNS. – mydoglixu Jan 17 '19 at 11:21

score 152 · Answer 2 · answered Sep 29 '12 at 06:39

152

Please try

RENAME TABLE  `oldTableName` TO  `newTableName`

answered Sep 29 '12 at 06:39

Vijay Verma

3,660
2
19
27

See [the answer above](https://stackoverflow.com/a/12650388/3397217) for why you should consider using the backticks. @coder – LinusGeffarth Nov 20 '17 at 17:21

score 42 · Answer 3 · edited Oct 07 '21 at 02:44

42

The MySQL syntax for RENAME TABLE statement is the following:

RENAME TABLE <old_table_name> TO <new_table_name>

In your query, you've used group which is one of the keywords in MySQL. Try to avoid MySQL keywords for names while creating tables, field names and so on.

edited Oct 07 '21 at 02:44

informatik01

16,038
10
74
104

answered Feb 06 '15 at 13:01

phponwebsites

651
6
9

score 28 · Answer 4 · answered Jun 13 '16 at 09:47

28

ALTER TABLE old_table_name RENAME new_table_name;

or

RENAME TABLE old_table_name TO new_table_name;

answered Jun 13 '16 at 09:47

Neeraj Kumar

6,045
2
31
23

A.A Noman · Answer 5 · 2019-11-22T16:23:47.777

24

Table name change

RENAME TABLE old_table_name TO new_table_name;

edited Nov 22 '19 at 16:23

answered Apr 02 '17 at 11:04

A.A Noman

5,244
9
24
46

Hasib Kamal Chowdhury · Answer 6 · 2018-09-20T10:28:18.117

20

Rename a table in MySQL :

ALTER TABLE current_name RENAME new_name;

edited Sep 20 '18 at 10:28

answered May 06 '17 at 17:13

Hasib Kamal Chowdhury

2,476
26
28

5

Welcome to SO. Please read this [how-to-answer](http://stackoverflow.com/help/how-to-answer) for providing quality answer. Just show code is not a good practice. – thewaywewere May 06 '17 at 17:36

score 14 · Answer 7 · answered Aug 18 '16 at 19:23

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`;

score 13 · Answer 8 · answered Aug 03 '15 at 08:18

13

ALTER TABLE `group` RENAME `member`

group is keyword so you must have to enclose into group

answered Aug 03 '15 at 08:18

umar_

491
5
17

score 12 · Answer 9 · answered Dec 10 '19 at 23:32

12

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 `

answered Dec 10 '19 at 23:32

HA S

1,129
12
10

Koech · Answer 10 · 2016-04-02T17:00:34.973

9

RENAME TABLE tb1 TO tb2;

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

edited Apr 02 '16 at 17:00

answered Mar 18 '16 at 08:02

Koech

415
6
6

score 9 · Answer 11 · answered Mar 10 '19 at 18:04

9

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`;

answered Mar 10 '19 at 18:04

sam-6174

3,104
1
33
34

score 6 · Answer 12 · answered May 13 '16 at 10:40

6

Try any of these

RENAME TABLE `group` TO `member`;

or

ALTER TABLE `group` RENAME `member`;

answered May 13 '16 at 10:40

Mohammedshafeek C S

1,916
2
16
26

Sushang Agnihotri · Answer 13 · 2021-03-24T05:57:11.820

5

Rename table
Syntax The syntax to rename a table in MySQL is:

ALTER TABLE table_name
RENAME TO new_table_name;

Example
Let's look at an example that shows how to rename a table in MySQL using the ALTER TABLE statement.
or example:

ALTER TABLE contacts
RENAME TO people;

edited Mar 24 '21 at 05:57

answered Mar 24 '21 at 05:49

Sushang Agnihotri

638
7
11

score 2 · Answer 14 · answered Dec 11 '18 at 03:34

2

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

answered Dec 11 '18 at 03:34

curiosity

834
8
20

score 2 · Answer 15 · answered Nov 03 '19 at 05:15

2

You can use

RENAME TABLE `group` TO `member`;

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

answered Nov 03 '19 at 05:15

Sodrul Amin Shaon

365
3
16

Vishnuvardhan · Answer 16 · 2018-01-08T05:32:30.543

-1

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;

edited Jan 08 '18 at 05:32

answered Jan 03 '18 at 07:49

Vishnuvardhan

5,081
1
17
33

score -6 · Answer 17 · answered Oct 16 '15 at 21:35

-6

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.

answered Oct 16 '15 at 21:35

Loai Tayem

61
8

MySQL not Transact SQL – Adam Joseph Looze Feb 17 '16 at 02:25

Rename a table in MySQL

17 Answers17

Linked

Related