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

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