Skip navigation links

MySQL Forums :: PHP :: Using data from one table to update another table


Advanced Search

Using data from one table to update another table
Posted by: Jeff Norton ()
Date: March 23, 2006 08:57PM

I have two tables one called Vendor and the other called Inventory

++++++++++++++
+ Vendor
++++++++++++++
+Vendor_ID
+Vendor_Name
++++++++++++++

+++++++++++++++
+ Inventory
+++++++++++++++
+ Description
+ Vendor_Name
+ Price
+ Weight
+++++++++++++++

Currently the Vendor_Name in both tables has the same value so I can do

WHERE Vendor.Vendor_Name = Inventory.Vendor_Name

Now the hard part. I would like to run a query that Updates the Inventory.Vendor_Name table with the value of the Vendor.Vendor_ID

T-SQL does it this way

UPDATE Inventory
SET Inventory.Vendor_Name = (SELECT Vendor.Vendor_ID
FROM Vendor, Inventory
WHERE Vendor.Vendor_Name = Inventory.Vendor_Name);

But this will not work in MySQL

The statement

SELECT Vendor.Vendor_ID
FROM Vendor, Inventory
WHERE Vendor.Vendor_Name = Inventory.Vendor_Name

works great on its own but I really need to get this in an UPDATE statement

Any ideas would be greatly appreciated and don't say go back to Oracle because thats not a option!

Options: ReplyQuote


Subject Written By Posted
Using data from one table to update another table Jeff Norton 03/23/2006 08:57PM
Re: Using data from one table to update another table Bob Field 03/23/2006 09:29PM
Re: Using data from one table to update another table Jeff Norton 03/23/2006 09:48PM
Using specific row from one table to update another specific row in another table y k 04/26/2006 12:51AM
Re: Using specific row from one table to update another specific row in another table Peter Brawley 04/26/2006 07:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.