Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating a (complex) view
It depends on the version. In 7.3 many of the join view issues are relaxed and under certain conditions you can update a join view.
Your example however would be updatable in all versions if you use EXISTS instead of a join view. For example:
create view myview
as
select x, y, z
from mytable
where exists ( select NULL from myothertable where p = mytable.x );
On Sat, 05 Apr 1997 09:21:09 -0800, Venu Subramaniam <venusub_at_popd.ix.netcom.com> wrote:
>I am pretty new to Oracle and am designing a 2-tier architecture which
>uses View-based security.
>
>I am trying to update a View which is using columns from one underlying
>table but references a column from another table. Is this a complex
>view? I am not able to Update such a View. Let me give an example.
>
>If my view is composed by
>
>CREATE VIEW MYVIEW AS
>SELECT x, y, z from MYTABLE, MYOTHERTABLE where x=MYOTHERTABLE.p;
>
>In the above View, the columns x,y,z all come from MYTABLE and a
>reference is made to column p in MYOTHERTABLE.
>
>I am not able to Update MYVIEW. Can I do this in any other way?
>Security is my primary concern and I'd prefer users not to update any
>tables directly.
>Thanks a lot for any help.
>- Venu
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities