Re: Why aren't UNION ALL updatable ?

Comments inline....

<<snip>>

> > Your update statement updates a set, a set without a single underlying
> > table. This set is definitely not a key preserved one.
>
> But if you don't eliminate duplicates, each row in the set corresponds
> exactly with a row from the table.

It is true that each row in the result set corresponds to exactly one row from each of the tables. But the result set is in effect just that, a set. Now that it is a set distinct from the source tables, it does not have any true reference to the source tables. Oracle is not designed to maintain the mappings from the result set to the original, source sets. Therefore, you can not update the rows from a UNION ALL view since the mapping back to the original source sets is lost!  

> > Also how would you think Oracle would be capable of distinguishing
> > which table the individual record comes from..
>
> For example, by generating the rowid when it fetches the row from the
> table and attaching it to the set.
>
> In principle, one could even rewrite the update from
> update ppv set x = 1;
> to
> update pp1 set x = 1; update pp2 set x = 1;

Since the sets in pp1 and pp2 are real tables, there is a mapping back to the rows. Since the set in ppv is not a real table, there is no "mapping". If you want to update, you'll have to do it like your last example.

I suppose Oracle could have written the RDBMS code so that you can update across a UNION ALL. But they chose not to.

This all comes from set theory which is the basis of Relational Database Systems. In tradtional set theory, there is no UNION ALL, only UNION which removes duplicate elements of the set. If duplicates are removed, then how can you determine which source set it came from? For instance:

   set A contains {1,2,3}
   set B contains {3,4,5}

   A UNION B = {1,2,3,4,5} Which set does '3' come from? A or B? If I update the UNION, which source set gets updated, A, B, or both? To resolve any ambiguity, updates are not allowed across a UNION view.

UNION ALL is an extension to UNION. It just leaves the duplicates. This means that

   A UNION ALL B = {1,2,3,3,4,5} Which '3' corresponds to which source set? In set theory, none since there is no true mapping due to the fact that this is a unique set all on its own.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================