Need help with the Update command

[comadreja]

Hi, i recently started using mysql and have a problem. How can i run a multi-table update? I mean, update one table regarding the results of a search in another table, like this:
UPDATE software,hardware SET software.name = 'example' WHERE sofwtare.company IN (SELECT hardware.company FROM hadware WHERE hardware.country = 'Spain')

It gives me a syntax error in the select part, and i don´t know why, guess is something stupid but i don´t get it. Can you help me?
If i run this update, in the update.log would appear like that or with the select part already processed?
Thank you very much!

 

[sleipnir214]

It's the subselect. Your version of MySQL may not support it.

Take a look here:

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[swampBoogie]

Mysql does not support subselect.

UPDATE software,hardware SET software.name = 'example' WHERE sofwtare.company = hardware.company 
and hardware.country = 'Spain'

May work, requires version 4.0.x though. Take a look at the update statement in the docs, there seems to be some problems with 1 to many relations.