Update

From: Ranko Mosic <ranko.mosic_at_gmail.com>
Date: Mon, 12 Mar 2007 14:37:21 -0400
Message-ID: <367369f10703121137j365ab664p5a4a329e5e50bd5d@mail.gmail.com>

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_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 12 2007 - 13:37:21 CDT