Help with running updates on joined tables

[throwdini]

If I have two tables (A and B) joined on the primary key and I want to update a field in table B based on criteria in table A, is there any way to do this?

Any help is greatly appreciated.
Thanks,
Jay

 

[KarveR]

Something like:
UPDATE tableB set thing = 'value' where tableB.id = tableA.id and tableA.something = 'whatever';
______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[throwdini]

My understanding was that mysql didn't support multi-table updates. I will try the method you mention, but have you successfully done it this way in mysql?

Also, I believe that it is possible to accomplish this kind of multi-talbe update through MS access. Why would it work with the ODBC drivers and access, but not work directly in mysql?

Thanks for the suggestion. Anything else will also be appreciated.

 

[petersJazz]

Its not possible in mySQL and its not standard SQL to be able to do update based on values in another table. You have to do it programmaticaly or if it is a one time shoot:

do a select that makes up the correct update statements, like:

select 'update tableB set thing = ' ||
a.value ||
' where id = ' ||
b.id ||
';'
from tablea a,
tableb b
where a.id = b.id;

now you will get a lot of update statements in the result. Copy and paste it and run the updates.