$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
John
----- Original Message ----- From: "Micah Stevens" <micah@xxxxxxxxxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Friday, August 27, 2004 5:13 AM
Subject: Re: 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
as LEFT JOIN.
Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.
-Micah
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 vendorprices.VendorItemNumber";
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.
Chris
-- 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
