Re: update and join?

Cory Hicks [mailto:chicks@tri-intl.com] wrote: > Hello to all! > > Quick question....is it possible to do an update query w/ a > join yet? If not, what is your preferred method? Would you > take care of it on the PHP side? No, it's not possible, at least not with MySQL 3.x. MySQL 4.1, currently in development, will feature nested subqueries and multi-table-updates. > > I need to update a table w/ data from another table if > certain conditions are true, i.e the fields in the table to > be joined are "NULL".... > I have to do that a couple of times, and always do it this way: Build a SELECT statement, selecting all the data you will need in the updates, and the primary key for the table which needs updates. Fetch all the data in a nested array, like this: $a = array(<primary key 1> => array ("col1"=>"data1","col3"=>"data3"), <primary key 2> => array ("col2"=>"data2","col3"=>"data3"), ....); and then, do the updates in a neat foreach loop: foreach ($a as $pkey=>$data) { [ build update stmt from $data array ] dbquery ($stmt) } Hope this helps. Thomas