Re: thinking about UPDATE

Marshall Spight wrote:
> I was thinking about the relationship between operations of the
> relational algebra and SQL statements.
>
> INSERT is a lot like union, but with an odd restriction that the
> intersection of the new set and the existing set must be empty. (Why?)

To introduce a piece of arbitrariness into the RM? I agree with your observation and your implied answer to the question. It might be reasonable to generate a warning (in a form that an application could ignore if it desired) if the cardinality of the target relation does not increase by the cardinality of the set of values inserted into the relation, but it should not be an error - precisely because the UNION operation does not make it an error. I personally think the asymmetry on INSERT is a hangover from the record-oriented approach, and it's one of the very few (if not the only one) left in CJD's RM.

I note that if you accept that an INSERT can deal with copies of existing rows without error, then the description of how to update a union view becomes much simpler.

> DELETE is the same thing as set subtraction.
>
> But what the heck is UPDATE?
>
> If T : {a1, a2, ... an}
>
> UPDATE T set a1 = 0 where <cond>
> is
> T' = T { 0, a2, ... an | <cond> }
> T = T - T { a1, a2, ... an | <cond> }
> T = T union T'
>
> That's certainly more complicated than I would suspect
> for what seems like such a simple operation.
>
> Am I making it needlessly complex, or is this about right?
> Is there some other way to think about UPDATE?

Give or take a bit, that's how Chris Date views UPDATE. He actually uses EXTEND to add the new (updated) values to the relevant subset of T, then projects away the original values of the updated columns, renames the new columns, and assigns the new relation to the original.

In his 'Logical Differences' paper on at http://www.dbdebunk.com/, he shows:

UPDATE P WHERE CITY = 'Paris' { CITY = 'Rome' };

(changes parts relation P so that parts currently shown in Paris are actually in Rome). The equivalent assignment is:

P := WITH
      (EXTEND P ADD (IF CITY = 'Paris' THEN 'Rome' ELSE CITY) AS NEW_CITY ) AS T1,

      T1 { ALL BUT CITY } AS T2 :
      T2 RENAME NEW_CITY AS CITY;

Your sequence using difference to remove the updated rows and union to replace them with the modified version is equivalent.

Yes, UPDATE is a complicated shorthand.

Now - work out how to deal with updates via pseudo-variables. That I've not gotten my brain around yet.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/