I tried something similar, though only updating one table, where the columns matched, and got errors:

$query="UPDATE t1 SET t1.f1=t2.f2 WHERE t1.f3=t2.f4";
query failed: Unknown table 't2' in where clause. However, t2 clearly does exist.

Using nested query:

$query="UPDATE t1 SET t1.f1=(SELECT t2.f2 FROM t2WHERE t2.f3='412') WHERE t1.f4='412'";
Error in query syntax near SELECT t2.f2 FROM t2WHERE t2.f3=

I was trying to update a column in t1 where there is a unique join with another table on another field of t1.

Should I use "UPDATE t1 INNER JOIN t2 ON t1.f3=t2.f4 SET t1.f1=t2.f2 WHERE t1.f3=t2.f4"??

Also, what happens if in the example below, items.id=month.id, there is more than one matching item in month.id?

Using PHP 4.3.4


----- Original Message ----- From: "Micah Stevens" <[EMAIL PROTECTED]>
Sent: Friday, August 27, 2004 5:13 AM
Subject: Re: [PHP-DB] Updating a table when using LEFT JOIN

From the Mysql docs:

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but multiple-table
UPDATE statements can use any type of join allowed in SELECT statements, such

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.


On Thursday 26 August 2004 03:23 pm, Chris Payne wrote:
Hi there everyone,

I am using the following to grab the data I need from several tables:

$sql = "SELECT * FROM vendorprices LEFT JOIN fooditems on
(vendorprices.FoodItemNumber = fooditems.FoodItemID) WHERE
vendorprices.VendorNumber='$VendorID' ORDER BY

This works great, very fast etc .. the problem is, I then need to give the
option for them to edit the items - again, not a problem in populating the
form - until I have to then use the UPDATE function, how can I update each
item in separate tables when I use the above join to grab the info in the
first place?  I've never had to write to 2 tables at once where data is
relative like this, infact it was my first time of using JOINS at all to
even display the data.

I'm using PHP with MySQL.

Thank you.


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

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

Reply via email to