Using data from one table to update another table
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!
Subject
Written By
Posted
Using data from one table to update another table
March 23, 2006 08:57PM
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.