Re: [PHP-DB] update and join?

[Thomas Lamy]

Cory Hicks [mailto:[EMAIL PROTECTED]] wrote:
 Hello to all!
 
 Quick questionis 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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] update and join?

[John W. Holmes]

 Quick questionis 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?

Probably depends on your database, but MySQL doesn't support it. I think
MySQL 4.0 might.

 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

You'll have to use a scripting language, or something outside of the
database. Or maybe a INSERT INTO ... SELECT ... will help?

---John Holmes...



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php