Update table without where exists( subquery) ?

From: Maggie <maggiezhou_at_hotmail.com>
Date: Thu, 30 Mar 2000 10:56:50 -0800
Message-ID: <8c081v$6gr$1@web1.cup.hp.com>

I try to update table ORDERS that order_nr and item_nr are same in the MR11 table and item_delivery is sth like the null or 71 in mr11 table. I created the following query by using where exists( subquery) but take extremely long time when it update thousands of rows:

update ORDERS o
  set(

   cust_name               ,
   order_nr                   ,
   item                          ,
   orderr_qty                ,

   order_enter_date
 )
   =
 ( select r11.customer_nm ,

         r11.sales_order_nr              ,
        r11.item_nr   ,
        r11.order_qt  ,
        r11.order_date

    from mr11 r11
    where (o.order_nr = r11.sales_order_nr)

         and (o.item = r11.ITEM_NR)
         and (r11.item_delivery = '71' or r11.item_delivery is null)

   )
  where exists

       (select * from mr11 r11
        where (o.order_nr = r11.sales_order_nr)
          and (o.item = r11.ITEM_NR)
         and (r11.item_delivery = '71' or r11.item_delivery is null)
       )

;

Is there any method that I can avoid to use where exists(subquery)?

Thanks Received on Thu Mar 30 2000 - 12:56:50 CST