Re: insert to projection
Date: Fri, 4 Sep 2009 20:21:45 -0700 (PDT)
Message-ID: <1ae7703a-db49-4489-a3ec-e48d1438d16d_at_a37g2000prf.googlegroups.com>
On Sep 4, 7:38 pm, paul c <toledobythe..._at_oohay.ac> wrote:
> Tegiri Nenashi wrote:
> > ...
> > I suggest that the idea that we focus on basic relational algebra
> > operators, figure out which updates of these are legitimate, then
> > leverage this knowledge for complex view updates is fundamentally
> > wrong. Consider a "pivot" view:
>
> > table Contacts (
> > name String,
> > email String,
> > phone String
> > )
>
> > and the view
>
> > select name, email as contact, 'email' as type
> > union
> > select name, phone as contact, 'phone' as type
>
> > This is a perfectly updatable view, yet your approach would fail to
> > discover how to translate its update to the base table. (This pivot
> > operation is a recurrent theme in data exchanges)
> > ...
>
> 1) I don't know what 'as' means here.
typo:
select name, email as contact, 'email' as type
from Contacts ^^^^^^^^^^^^
union
select name, phone as contact, 'phone' as type
from Contacts ^^^^^^^^^^^
'as' in SQL is rename operation.
The base table
[name email phone]
Max max_at_yahoo.com 650-436-7445
Claire claire_at_gmail.com 415-325-7619
is viewed as
[name contact type]
Max max_at_yahoo.com email
Max 650-436-7445 phone
Claire claire_at_gmail.com email
Claire 415-325-7619 phone
This view is updateable.
> 2) I haven't talked about updating a union, but if I had I would have
> distinguised between insert and delete. In this thread I've only had
> projections in mind.
This is technically more complex than just a union view. It also involves renaming and another table with two tuples {<phone>,<email>}. Received on Sat Sep 05 2009 - 05:21:45 CEST