MySQL Update error while updating table

Question

I have table in MySQL with years as column names like 1960, 1961, 1962... etc. Records are being inserted successfully. When I try to update table with query

UPDATE table1 SET 1960=0.0 WHERE id = 'abc'

it gives:

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 '1960=0.0 WHERE id='abc' at line 1

Is it due to columns names as numbers or something else is wrong?

Thats a very bad choice of naming columns. Anyway, try to surround 1960 with a pair of backtick operator.. — UltraInstinct
– UltraInstinct, Commented Jul 17, 2012 at 14:18

Brian Adkins · Accepted Answer · 2012-07-17 14:17:02Z

3

try this:

UPDATE table1 SET `1960`='0.0' WHERE id = 'abc'

... added backticks to column name and single quotes around value (not really required for the value, but I always do it)

answered Jul 17, 2012 at 14:17

Brian Adkins

6662 gold badges6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2012-07-17 14:17:17Z

3

escape your column name with backticks

UPDATE table1 SET `1960` = 0.0 WHERE id = 'abc'

That has to be done if your column name is a reserved keyword in MySQL or a number.

answered Jul 17, 2012 at 14:17

juergen d

205k40 gold badges303 silver badges377 bronze badges

Comments

Andy Jones · Accepted Answer · 2012-07-17 14:20:36Z

0

You'll have to escape your column names by using the backtick character. The following manual page is somewhat dense but informative

Try...

UPDATE table1 SET `1960`=0.0 WHERE id = 'abc'

answered Jul 17, 2012 at 14:20

Andy Jones

6,2804 gold badges33 silver badges47 bronze badges

Collectives™ on Stack Overflow

MySQL Update error while updating table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related