Re: Why aren't UNION ALL updatable ?

Your view is a join of two tables, including an implicit order by. Those views are not updatable by definition (as defined in the sql reference manual).
Your update statement updates a set, a set without a single underlying table. This set is definitely not a key preserved one. Also how would you think Oracle would be capable of distinguishing which table the individual record comes from.. If you really want to do this (which IMHO violates most relational rules, and you could also question why you have two *identical* tables, if I were acting as a DBA on your server, you would have to explain this to me, because you will inevitably create havoc), you need instead of triggers as available from Oracle 8i.

Hth,

Sybrand Bakker, Oracle DBA

<dellera_at_my-deja.com> wrote in message news:965opg$nkp$1_at_nnrp1.deja.com...
> Dear all,
>
> I was wondering why it's not possible to update a view
> which is the UNION ALL (please note: UNION ALL, not UNION)
> of two identical tables:
>
> SQL> create table pp1 (x number);
>
> Table created.
>
> SQL> create table pp2 (x number);
>
> Table created.
>
> SQL> create or replace view ppv as
> select * from pp1
> union all
> select * from pp2
> ;
>
> View created.
>
> SQL> update ppv set x = 1;
> update ppv set x = 1
> *
> ERROR at line 1:
> ORA-01732: data manipulation operation not legal on this view
>
> IMHO, "update ppv set x = 1;" may be seen equivalent to
> "update pp1 set x = 1; update pp2 set x = 1;", and so rewritten
> on the fly by the Oracle engine.
>
> Of course I know that I can use partitioning, and I also
> know that there is the possibility to use PARTITIONED VIEWS
> (even if the latter is a deprecated feature); my question
> is more theoretical, namely if there's any underlying strong
> reason to avoid this. For instance, I would call this view a
> key-preserved one ..
>
> Any idea to clear my puzzled mind ?
>
> Thanks in advance
> Alberto
>
>
>
>
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sun Feb 11 2001 - 04:56:21 CST