oracle - Update using loop in Oracle

Question

BEGIN
 FOR J IN (select DISTINCT  S_NUMBER from WMWHSE3.NUMBER_SHIPTO)
 LOOP
UPDATE wmwhse3.orders
SET
    orders.susr4 = (select S_NUMBER from WMWHSE3.NUMBER_SHIPTO where S_NUMBER = J)
where     orders.c_company = (select SHIPTO from WMWHSE3.NUMBER_SHIPTO where S_NUMBER = J)
and orders.orderkey in (SELECT  a.orderkey FROM wmwhse3.orders a INNER JOIN wmwhse3.wavedetail b ON a.orderkey = b.orderkey where b.wavekey = '0000000086');
  END LOOP;
END;

标签： oracleplsqlsql-update

Answer 1

Looks like you're overcomplicating things (and making everything slower than it should be, as row-by-row processing (in a loop) is usually much slower than set processing).

Therefore, how about a single update, without PL/SQL? Something like this:

update orders o set
  o.susr4 = (select n.s_number
             from number_shipto n
             where n.shipto = o.c_company
            )
where exists (select null from wavedetail b
              where b.orderkey = o.orderkey
                and b.wavekey = '0000000086'
             );