Unknown column

Databases
1

Hello all!

Can someone help me figure this out, I am running a query and getting the error
#1054 - Unknown column ‘42’ in ‘where clause’

42 is not the column but the id, here is the query I am running, I checked it many times for errors and can’t find what it is!


UPDATE jml_fl_users SET `street`=`535 E 18th St`, `town`=`Flatbush - Ditmas Park`, `city`=`Kings`, `state`=`New York`, `country`=`United States`, `zipcode`=`11226`, `coordinates`=`40.63763458315378,-73.96082556740572`, `phone`=`4657345908`, `website`=`http://www.someurl.com`, `favoritebook`=`some book`, `aboutme`=`some details about me`, `birthdate`=`05-16-2012` WHERE `id` = `42`
2

yet another developer pwned by mysql’s evil backticks :smiley:

backticks are used to delimit identifiers, i.e. column and table names

everything in red below is regarded as a column name –


UPDATE jml_fl_users
   SET [color=red]`street`[/color] = [color=red]`535 E 18th St`[/color]
     , [color=red]`town`[/color] = [color=red]`Flatbush - Ditmas Park`[/color]
     , [color=red]`city`[/color] = [color=red]`Kings`[/color]
     , [color=red]`state`[/color] = [color=red]`New York`[/color]
     , [color=red]`country`[/color] = [color=red]`United States`[/color]
     , [color=red]`zipcode`[/color] = [color=red]`11226`[/color]
     , [color=red]`coordinates`[/color] = [color=red]`40.63763458315378, -73.96082556740572`[/color]
     , [color=red]`phone`[/color] = [color=red]`4657345908`[/color]
     , [color=red]`website`[/color] = [color=red]`http://www.someurl.com`[/color]
     , [color=red]`favoritebook`[/color] = [color=red]`some book`[/color]
     , [color=red]`aboutme`[/color] = [color=red]`some details about me`[/color]
     , [color=red]`birthdate`[/color] = [color=red]`05-16-2012`[/color]
 WHERE [color=red]`id`[/color]  =  [color=red]`42`[/color]

here’s my advice: don’t use backticks!!


UPDATE jml_fl_users
   SET street = '535 E 18th St'
     , town = 'Flatbush - Ditmas Park'
     , city = 'Kings'
     , state = 'New York'
     , country = 'United States'
     , zipcode = '11226'
     , coordinates = '40.63763458315378, -73.96082556740572'
     , phone = '4657345908'
     , website = 'http://www.someurl.com'
     , favoritebook = 'some book'
     , aboutme = 'some details about me'
     , birthdate = '05-16-2012'
 WHERE id = 42

note the birthdate value won’t insert properly if birthdate is an actual DATE datatype

note also there are no quotes around 42 because id is presumably a numeric column