Re: Update
- From: "Alexander Fatkulin" <afatkulin@xxxxxxxxx>
- To: ranko.mosic@xxxxxxxxx
- Date: Tue, 13 Mar 2007 10:52:07 +1000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
