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
John
- Original Message -
From: "Micah Stevens" <[EMAIL PROTECTED]>
To: <[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
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
