Error 1064 on Update Query 1

[sue0912]

Hi Everyone,

I am a bit new to MYSQL as I am SQLServer. So I am trying to do a simple update query (for zen cart) and keeping getting an Error 1064 syntax error.

What does MYSQL not like about my code? and can someone explain why?

UPDATE zen_categories
SET zen_categories.categories_image = 'bolt.gif'
FROM zen_categories, zen_categories_description
Where zen_categories.categories_id = zen_categories.categories_id AND zen_categories_description.categories_name = 'bolts'

Error Message
#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 'FROM zen_categories, zen_categories_description
Where zen_categories.categorie' at line 3

Thanks Sue

 

[IanWaterman]

I think your syntax should be

UPDATE zen_categories
SET zen_categories.categories_image = 'bolt.gif'
FROM zen_categories
inner join zen_categories_description
on zen_categories.categories_id = zen_categories.categories_id
where zen_categories_description.categories_name = 'bolts'

Ian

 

[sue0912]

I tried that and a Left Outer Join and I still get the same error message.

 

[IanWaterman]

Sorry just spotted that you are joining

zen_categories.categories_id = zen_categories.categories_id

which is the same table

Try

UPDATE zen_categories
SET zen_categories.categories_image = 'bolt.gif'
FROM zen_categories
inner join zen_categories_description
on zen_categories.categories_id = zen_categories_descriptions.categories_id
where zen_categories_description.categories_name = 'bolts'

Ian

 

[sue0912]

Thanks Ian, my bad.

but I still get the same error message. It is like it is looking for a quote or comma that I am missing. I just can not see it.

UPDATE zen_categories
SET zen_categories.categories_image = 'bolt.gif'
FROM zen_categories
inner join zen_categories_description
on zen_categories.categories_id = zen_categories_description.categories_id
where zen_categories_description.categories_name = 'bolts'

 

[tsuji]

Should this not do? (Make "From..." part disappear.)
[tt]
UPDATE zen_categories[blue],zzn_categories_description[/blue]
SET zen_categories.categories_image = 'bolt.gif'
Where zen_categories.categories_id = zen_categories_description.categories_id AND zen_categories_description.categories_name = 'bolts'
[/tt]

 

[sue0912]

That was it. Thank you.

Now, why does MYSQL not like FROM?

 

[tsuji]

mysql 5/6:

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

in contrast to mssql (typical):

http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

 

[sue0912]

cool. Thank you