Update

Hi List,
I need to update large table sub_svc_parm ( over billion rows).  This
is PL/SQL block that does it:
I want to rewrite so it is executed  as single SQL update statement.
Any suggestions ?

DECLARE

 v_sub_svc_id  sub_svc_parm.sub_svc_id%type;
 v_val         sub_svc_parm.val%type;

 CURSOR cur_dgid_update IS
   SELECT /*+ FIRST_ROWS */ b.sub_svc_id, d.destination_group_id
     FROM sub_svc a, sub_svc b, sub_svc_parm c, RATE_CENTRE_TEMP_&1 d
    WHERE a.SUB_ID = b.SUB_ID
      AND a.SVC_ID = 10000
      AND b.SVC_ID = 10008
      AND a.SUB_SVC_ID = c.SUB_SVC_ID
      AND c.parm_id = 10230
      AND SUBSTR(c.val,1,6) = d.npanxx;


BEGIN /* Main */

 OPEN cur_dgid_update;

 LOOP
   FETCH cur_dgid_update INTO v_sub_svc_id, v_val;
   EXIT WHEN cur_dgid_update%NOTFOUND;

   UPDATE sub_svc_parm
      SET VAL = v_val
    WHERE sub_svc_id = v_sub_svc_id
      AND parm_id = 12650;
 END LOOP;

 CLOSE cur_dgid_update;
--  COMMIT;
END;




--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
--
http://www.freelists.org/webpage/oracle-l


Other related posts: