Re: Update

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Tue, 13 Mar 2007 21:15:50 +1000
Message-ID: <49d668000703130415i59710daewa75b6225630218b2@mail.gmail.com>

Jaromir,

> 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 (however it's obvious what every row from A will not join to more than one row in B if (sub_svc_id, parm_id) is a unique pair).

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

Received on Tue Mar 13 2007 - 06:15:50 CDT