Re: Update view

In article <8c1ht4$uo$1_at_web1.cup.hp.com>, "Maggie" <maggiezhou_at_hotmail.com> wrote:
> When I try to update a view, I get an error:
>
> CREATE TABLE t1
> (
> c1 NUMBER,
> c2 NUMBER
> )
>
> CREATE TABLE t2
> (
> c1 NUMBER,
> c2 NUMBER
> )
>
> create view tt as
> select t1.c1, t1.c2, t2.c2 "c4"
> from t1, t2
> where t1.c1 = t2.c1
>
> update tt
> set c4 = c2
> and I get " ORA-00904: invalid column name "
>
> Please tell me why?
>
>

you put double quotes around the "c4" so it is in lower case and must ALWAYS be quoted.

You could either:

update tt set "c4" = c2

(but you'll get a different error, more on that in a minute) or you can

select t1.c1, t1.c2, t2.c2 c4
from t1, t2
where t1.c1 = t2.c1

but that'll get an error as well:

ops$tkyte_at_8i> update tt
  2 set c4 = c2
  3 /
set c4 = c2

    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

In order to update that view, you will need:

ops$tkyte_at_8i> alter table t1 add constraint t1_pk primary key(c1);

Table altered.

ops$tkyte_at_8i> update tt
  2 set c4 = c2
  3 /

0 rows updated.

It needs to know that each row in T2 will be joined to AT MOST 1 row in T1 and hence each row in T2 will appear AT MOST once in the view TT. If a row in T2 appears more then once in the view TT, the results of the update are in general "ambigous".

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 31 2000 - 18:33:06 CST