Re: insert to projection

From: Tegiri Nenashi <tegirinenashi_at_gmail.com>
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 Fri Sep 04 2009 - 22:21:45 CDT

Original text of this message