32

I have a table that has column names like 25, 50, 100, etc..

When trying to update the table I get an error, no matter how I do it

UPDATE table SET '25'='100' WHERE id = '1'

I have tried quoting and backticking every which way but without success.

The error is always along the lines of:

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 ''25'=100 WHERE id=1' at line 1

If I change the column name to twentyfive - I don't have a problem, but that's not what I want. Is it possible to use a number as a column name?

Ben
  • 51,770
  • 36
  • 127
  • 149
Daniel Hunter
  • 2,546
  • 6
  • 28
  • 34
  • 1
    I've provided an answer, but I'd still like to say that I think using numeric field names is a bit unorthodox, and I'd tend to just prefix the fields. – Matthew Nov 02 '11 at 03:29
  • refer to this post, http://stackoverflow.com/questions/41711470/laravel-how-to-access-column-with-number-name-of-a-table – LF00 May 05 '17 at 06:12

5 Answers5

53

From the docs:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Which means you must quote it with back ticks like `25`:

UPDATE table SET `25`='100' WHERE id='1'
Matthew
  • 47,584
  • 11
  • 86
  • 98
6

As others have said, you can back-tick the names of tables, columns, etc. Just make sure you're not back-ticking your values otherwise it'll interpret them as column names. So in your example only the 25 needs to be back ticked:

UPDATE table SET `25`=100 WHERE id=1
Bryan Moodie
  • 69
  • 1
2

If you need it to be based on a number for some reason, you can append some simple prefix to the number in all cases (e.g. "col25", "col87", etc).

Jon Newmuis
  • 25,722
  • 2
  • 45
  • 57
2

Check here: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So yes, you can do it -- you need to use backtics around the name.

gview
  • 14,876
  • 3
  • 46
  • 51
-1

Names in MySQL can start with a digit, but cannot be entirely digits — they cannot be confusable with an integer. So, no.

And if you start your identifier with a digit, you should be pretty sure you're sticking with MySQL because many (most?) other database engines require an alpha character at the front of column names.

(As stated in the comments, a fully integer column name is allowed if you consistently use the backtick character — if you tried backticks and still got a syntax error, post the precise backtick syntax you used).

Also, of course, you're going to produce some difficult to read SQL if you opt for integer-named columns!

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160