Re: Update

Hi Alexander,

>> 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.
>
> as long as I can understand from the above query+update (without
> further looking at DDL) the main problem here will be with
> sub_svc_parm table. Looks like it holds unique constraint on
> (sub_svc_id, parm_id) and part of the updatable join view will look like
>
> update (
> select b.val,...
> from sub_svc_parm a, sub_svc_parm b...
> where a.sub_svc_id=b.sub_svc_id
> and a.parm_id=10230
> and b.parm_id=12650
> ...
> ) set b.val=...
>
> this alone will make this to be a non-key preserved view from Oracle's
> perspective

I completely agree.
My point was, that even if you get rid of those problems with literal constraints, there is an *additional* problem with key preserving in this case caused by the substr in the join condition. A function on a FK column seems to stop transfering the key preserving information.

this works (xpk is unique)
update (select x1.xatt att1, x2.xatt att2

        from x1,x2
        where x1.xfk = x2.xpk)
        set att1 = att2;

this fails with ora-01779
update (select x1.xatt att1, x2.xatt att2

        from x1,x2
        where  x1.xfk||'' = x2.xpk)
        set att1 = att2;

somehow like the olds day "suppress index" feature:)

Regards,

Jaromir

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