Re: Update

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 13 Mar 2007 10:42:18 +0100 (CET)
Message-ID: <50037.213.162.65.17.1173778938.bloek@pwebmail.utanet.at>

Hi Alexander and Ranko,

notes in-line

> 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).
>

Additional problem here ist the join condition AND SUBSTR(c.val,1,6) = d.npanxx
where the key preserving information is aparently lost even if the column npanxx is declared as unique.

> I would try to use merge instead. Something like this (I've just typed
> it in, can't verify):
>

Good idea.
Please check if keys are preserved anyway, as runtime exception is raised if this is violated.
In my interpretation all of those columns must be (at least logically) unique

sub_svc.sub_svc_id
sub_svc.sub_id
sub_svc_parm.sub_svc_id

RATE_CENTRE_TEMP_&1.npanxx

Btw if you know this is fine you may use the updatable join view as well (googling for bypass_ujvc at your own risk:)

Regards,

Jaromir

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 13 2007 - 04:42:18 CDT