Thanks Alexander, MERGE should do the trick. It is 9i, so I'll have to supply fake not matched clause. Much appreciated. Ranko. On 3/12/07, Alexander Fatkulin <afatkulin@xxxxxxxxx> wrote:
Ranko, since you are almost certainly going ahead to ORA-01779 with such kind of query if you write it as a single "update (select ...) set ..." (Oracle doesn't like when you join tables with supplied variables/literals even if that leads to a fully key-preserved join). I would try to use merge instead. Something like this (I've just typed it in, can't verify): merge into sub_svc_parm ssp using ( SELECT b.sub_svc_id, d.destination_group_id val 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) v on (ssp.sub_svc_id=v.sub_svc_id and ssp.parm_id = 12650) when matched then update set ssp.val=v.val; btw - what version? I assume you're on 10g so you don't need both matched/not matched sections. On 3/13/07, Ranko Mosic <ranko.mosic@xxxxxxxxx> wrote: > 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 ? -- Alexander Fatkulin
-- 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 -- //www.freelists.org/webpage/oracle-l