MySQL Forums
Forum List  »  PHP

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




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.