Re: Update table without where exists( subquery) ?

Maggie <maggiezhou_at_hotmail.com> wrote in message news:8c081v$6gr$1_at_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
>
>

First of all, replace the * in your subquery by a 'x'. You are now forcing Oracle to retrieve the row, using 'x' it won't retrieve the row. Secondly, are you sure you have proper indexes? You may run into unwanted full table scans! Normally, exists is always fast, so there should be some other things at play.

Hth,
Sybrand Bakker, Oracle DBA Received on Thu Mar 30 2000 - 13:22:45 CST