Re: Update

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_at_gmail.com> 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_at_gmail.com> 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_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
--
http://www.freelists.org/webpage/oracle-l