Re: [PHP-DB] Updating a table when using LEFT JOIN

=46rom the Mysql docs:=20

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cove=
r=20
multiple tables:=20

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


The example shows an inner join using the comma operator, but multiple-tab=
le=20
UPDATE statements can use any type of join allowed in SELECT statements, su=
ch=20
as LEFT JOIN.=20


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


=2DMicah=20


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 =3D "SELECT * FROM vendorprices LEFT JOIN fooditems on
> (vendorprices.FoodItemNumber =3D fooditems.FoodItemID) WHERE
> vendorprices.VendorNumber=3D'$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

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" <micah@raincross-tech.com>
To: <php-db@lists.php.net>
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