Rename a MySQL column containing ` in the column name

Question

So, the other guy at work created a table with a column called:

    Max(`abs_spg_20090430`.`ID`)

this is giving me an error now that I am trying to run a dump of the database on a different server.

I am trying to rename it, but

    ALTER TABLE abs_spgID_20090504 CHANGE Max(`abs_spg_20090430`.`ID`) id bigint default null;

as well as

    ALTER TABLE abs_spgID_20090504 CHANGE `Max(`abs_spg_20090430`.`ID`)` id bigint default null;

give me an error. Does any of you friendly people have a hint? Many thanks!

The column name has parenthesis `( )` **and** backquotes? wow! — ypercubeᵀᴹ, Nov 05 '11 at 08:23
Please *always* quote the exact error message that you are getting. — Pekka, Nov 05 '11 at 08:27
`Max(`abs_spg_20090430`.`ID`)` is not a column, it's a function call — Pekka, Nov 05 '11 at 08:28
no, this other guy really did choose that name for the column. — Manuel Aguilera-López, Nov 05 '11 at 08:38

score 3 · Answer 1 · answered Nov 05 '11 at 08:29

3

you need to quote your quotes and the column too, e.g:

ALTER TABLE abs_spgID_20090504  CHANGE `Max(``abs_spg_20090430``.``ID``)` id BIGINT DEFAULT NULL;

answered Nov 05 '11 at 08:29

Kevin Burton

1

This almost work, but the server complained about the period now. I did create another table with the same fields, but more normal names, and then did a insert into tmp select * from abs_spgID_20090504; and then just delete the first table, and rename the second one. Thanks a lot anyways! I appreciate your input – Manuel Aguilera-López Nov 05 '11 at 08:40
strange, when I created the column exactly as stated the command worked fine for me – Kevin Burton Nov 05 '11 at 08:49

1 Answers1