I can't rename a mysql table name... it has a space in it

Question

I've imported a csv into MySQL. PHPMyAdmin helpfully created a table called TABLE 8 (with a space).

When I try to rename in SQL:

RENAME TABLE 8 to gender

I get the 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 '`TABLE 8` to `gender`' at line 1

I have tried back-ticks, quotes... seemingly everything...

I guess it's the space that's causing the problem but I'm out of ideas.

What's the name of the table `table 8`? `Rename table 'table 8' to 'gender'` where ticks are backticks — xQbert, Jul 16 '16 at 15:25
Yep worked for me on sql fiddle http://sqlfiddle.com/#!9/d989c/1/0 — xQbert, Jul 16 '16 at 15:28
Possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Jocelyn, Jul 16 '16 at 15:34
Possible duplicate of [Rename table with spaces mysql](http://stackoverflow.com/questions/9522708/rename-table-with-spaces-mysql) — Drew, Jul 16 '16 at 15:35
Possible duplicate of [Using backticks around field names](http://stackoverflow.com/questions/261455/using-backticks-around-field-names) — Jocelyn, Jul 16 '16 at 15:36
TBH it was an omission on the keyword TABLE not a back tick issue... so give me a break! ;-) (It's bad enough that I miss it in the first place!) — stilts77, Jul 16 '16 at 15:39
@stilts77 All programmers make mistakes, sometimes very simple ones! — Jocelyn, Jul 16 '16 at 15:58

score 6 · Accepted Answer · answered Jul 16 '16 at 15:27

6

The syntax is wrong, you're missing the table keyword (and yes, note the `s to escape the table name containing a space):

RENAME TABLE `TABLE 8` TO gender

Alternatively, you could use the fuller syntax:

ALTER TABLE `TABLE 8` RENAME TO gender

answered Jul 16 '16 at 15:27

Mureinik

297,002
52
306
350

I can't rename a mysql table name... it has a space in it

1 Answers1