Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    17

    Red face Unanswered: HELP with Update with Join

    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!

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    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)

  3. #3
    Join Date
    Feb 2003
    Posts
    17
    It works... Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •