Re: How can I update table without cross joins or where in (...)?

Try
 update ordq.testmr11 m
  set order_qty =0
  where
not exists
 (select 'x'

     from sumorders_tmp s
     where   s.sales_order_nr = m.order_nr
     and s.extd_sales_order_item_nr = m.item

If sumorders_tmp is properly indexed (concatenated index on sales_order_nr, extd_saled_order_item_nr) should work like a charm.

Hth,

Sybrand Bakker, Oracle DBA

Maggie <maggiezhou_at_hotmail.com> schreef in berichtnieuws 8d2gte$17g$1_at_web1.cup.hp.com...
> How can I update table without cross joins or where in (...)?
>
> I'm trying to update table mr11 which records do not exist in table stmp.
>
> (1) first , I tried this --- BUT IT'S VERY SLOW for only 3000 rows.
> update ordq.testmr11 m
> set order_qty =0
> where (m.order_nr, m.item) not in
> (select s.sales_order_nr, s.extd_sales_order_item_nr from sumorders_tmp s)
>
> (2) Then, I found a quick way only take a second for query:
>
> select m.order_nr, m.item, s.sales_order_nr
> from mr11 m, sumorders_tmp s
> where m.order_nr = s.sales_order_nr(+) and m.item =
> s.extd_sales_order_item_nr(+)
> and s.sales_order_nr is null and s.extd_sales_order_item_nr is null
>
> Hence, I try to use UPDATE in the same way:
>
> update mr11 m
> set order_qty =0
> from mr11 m, stmp s
> where m.order_nr = s.sales_order_nr(+) and m.item =
> s.extd_sales_order_item_nr(+)
> and s.sales_order_nr is null and s.extd_sales_order_item_nr is null
>
> Of course, it doesn't work. However, MS SQL server does work in this way
> and very fast. I really want to know the other quick way to do this in
> Oracle before my colleagues say Oracle is stupid.
>
> Thanks.
>
>
Received on Wed Apr 12 2000 - 00:00:00 CDT