Re: update row in a view

In article <gdvj7scemuaeep32col63rnqsbm9i38533_at_4ax.com>,   ixes_at_gmx.de wrote:
> Hallo!
>
> I have a view like
> select t1.c1 t2.c1 from t1,t2 where t1.c2 = t2.c2.
> I canīt update rows in this view.
> What did i wrong?
>
> Any help would be great!
>
> Thomas Meiers
>

In order to update rows returned from a join view the rows selected by the view must create what is called a key preserved table and you must be on version 7.3 or higher.

Basically a key preserved table is a result set where one of the column values in the result set uniquely identifies one row and only one row in the target table of the DML statement. Using the emp and dept tables as an example if the view returns the empno (employee number) which is the unique key to the emp table and also returns only one row per empno then you should be able to use this view to perform DML. You can look at the dictionary table all_updatable_columns to see if you can update via the view. Note the view changed between version 7 where it just listed the view as updatable or not and ver 8 where it specified whether or not you can insert, update, and delete per column. Also I prefer to join to this view to all_views to filter out tables.

Naturally there are restrictions on the view having group by, distinct, nested tables, etc....

For ver 8 ch 13 of the DBA Admin manual covers the topic pretty well.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 10 2000 - 11:18:26 CST