Major simple update question

[edelwater]

#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 ''LR1' SET LEFT='1' WHERE ID='1'' at line 1"

I try the very simple (with all integers):

UPDATE LR1 SET LEFT=1 WHERE ID=1 or
UPDATE LR1 SET LEFT=1 WHERE ID='1' or
UPDATE LR1 SET LEFT='1' WHERE ID='1' or
UPDATE 'LR10 SET LEFT='1' WHERE ID='1'

*argh*

--

http://leau.net

 

[feherke]

Hi

And what is the structure of your table ?

Feherke.

http://rootshell.be/~feherke/

 

[edelwater]

ID int(11)
LEFT int(11)
RIGHT int(11)
NAME varchar(150)
CAT varchar(150)

--

http://leau.net

 

[TonyGroves]

LEFT is a keyword. Change the name if you can; if you can't, then you will have to enclose it in backticks:
[tt]
SET `left`=1
[/tt]

 

[edelwater]

ach...$#"$#" ...lol hahaha you are RIGHT. tnx.

--

http://leau.net

 

[r937]

this is why when you see that particular structure described in a tutorial or article, the columns are always called lft and rgt



r937.com | rudy.ca

 

[ade234uk]

I suppose this is why I could not work out when I first started out, why the field called password in the database was not accepting my login details.

hahahahah silly me... We all do it.

 

[shadedecho]

moreover, i am pretty sure you cannot do:

update...where...or update....where........

you can't string multiple occurences of update together like that. moreover, you don't need to. the query above is trying to update one table called LR1 (i am assuming 'LR10 in the 4th line was a typo!) with 4 possible values based on another discrete value in each record. this can nicely be done with a CASE statement:

update LR1 set LFT = (CASE ID WHEN 1 THEN 1 WHEN '1' THEN '1' .... END)

no need for where clauses or OR's... in fact your previous syntax is invalid, and it should be done more like I am suggesting. hope this helps.

 

[shadedecho]

furthermore, if 'LR10 was not a typo, but trying to reference another table than LR1, then you need a multiple table syntax... check this doc to see how:

http://dev.mysql.com/doc/refman/4.1/en/update.html