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

    Unhappy Unanswered: HELP with DB2 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
    Feb 2003
    Posts
    17
    Got the Answer... Thanks!

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you mind posting what you found ?

    Thanks

    Sathyaram
    Originally posted by studrew
    Got the Answer... Thanks!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    DB2 Universal Vers 8.

    Probably, studrew used a statement like

    update wareh c set c.reorder_lv =
    (select 5
    from product a
    where a.vendor_id = '1' and a.prod_id = c.prod_id)

    "saludos", arlf.

  5. #5
    Join Date
    Feb 2003
    Posts
    17
    Sorry, should have posted the answer. It was submitted by member "Ida Hoe"

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

    Works perfectly.

    Studrew

Posting Permissions

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