I’ve been trying to get this to work for a while now and I can’t find anything in the forum that works. I am using DB2 UDB v8.1 for windows. Here the problem:

I have 2 tables…

Product Table
-------------
ID (Primary key)
Vendor_ID

Warehouse Table
--------------
Product_ID (Foreign key to Product.ID)
Reorder_Level


I want to update all ‘Warehouse.Reorder_Level’ on product’s that have a Vendor_ID of 1.

I have tried the following statements without any luck:

Update Warehouse set Reorder_Level=5 from Warehouse inner join Product on (Warehouse.Product_ID = Product.ID and Product.Vendor_ID=1)

Update (Select Warehouse.* from Product, Warehouse where Product.ID = Warehouse.Product_ID and Product.Vendor_ID=1) as temp set temp.Reorder_Level
=5

Update Warehouse, Product set Warehouse.Reorder_Level=5 where Product.ID = Warehouse.Product_ID and Product.Vendor_ID=1

I’m out of ideas and very frustrated… Please HELP!

I don't know db2, but msql would be

UPDATE Warehouse
SET Reorder_level = 5
WHERE EXISTS
(SELECT *
FROM product
WHERE id = product_id AND vendor_id = 1)

It works... Thank you!