Path: utzoo!mnetor!uunet!lll-winken!lll-lcc!pyramid!infmx!aland
From: aland@infmx.UUCP (Dr. Scump)
Newsgroups: comp.databases
Subject: Re: Quel vs SQL in Ingres 5.0
Message-ID: <135@infmx.UUCP>
Date: 13 Apr 88 06:53:33 GMT
References: <4350@ihlpf.ATT.COM> <551@hscfvax.harvard.edu>
Organization: Informix Software Inc., Menlo Park, CA.
Lines: 56
Keywords: quel, sql, ingres, informix, correlated subquery
Summary: Updates with correlated subqueries in SQL

In article <551@hscfvax.harvard.edu>, pavlov@hscfvax.harvard.edu (G.Pavlov) writes:
> 
> 3. We use QUEL almost exclusively; we believe it to be more powerful.  How,
>    you might ask ?  To be honest, I can't say much, since it has been a long
>    time since I have had the privilege of having to use SQL.  But one example
>    comes to mind:  during our dbms evalutaion, the following proved to be 
>    easy to do in QUEL and a pain in SQL.  I only list the QUEL version, since
>    I do not remember what was required from SQL and I do not want to prejud-
>    ice it with an overly complex solution.  Maybe someone else can help out.
>
>    Application:  update attribute a1 in table a with attribute b1 in table b,
>    wherever the value of a2 in table a is the same as the value of b2 in 
>    table b.
> 
>    QUEL:
> 
>     replace a (a1=b.b1) where a2.a = b2.b
                                -----------  Are these correct? Does QUEL
                                             switch to attribute.table
                                             notation for WHERE clauses
                                             instead of table.attribute?
> 
>      greg pavlov, fstrf, amherst, ny
> 

In SQL, this update requires what is known as a Correlated Subquery (since
a subquery is used which correlates the table being updated to value(s) in
some other table).

The equivalent SQL UPDATE syntax:

(I can't remember for sure if the IN clause is Codd or ANSI standard,
so I will give two (equivalent) possibilities for the main WHERE clause.
Either form is acceptable in Informix SQL-based products)

   update a
     set a.a1 = (select b1 from b where a.a2 = b.b2)
     where a.a2 in (select b2 from b)  
or:
   update a
     set a.a1 = (select b1 from b where a.a2 = b.b2)
     where a.a2 = any (select b2 from b)  

The main WHERE clause (applies to the update, not the subquery) may be
omitted if for every value for a2 in table a there exists a b2 in table
b.  If the main WHERE clause is omitted and there are some values for
a2 which do *not* exist in b2, the a1 column for those rows in a are 
set to NULL (thus the potential need for the main WHERE clause).

Say *that* three times fast! :-]


-- 
 Alan S. Denney  |  Informix Software, Inc.  |  {pyramid|uunet}!infmx!aland
    CAUTION: Objects on terminal are closer than they appear...
 Disclaimer: These opinions are mine alone.  If I am caught or killed,
             the secretary will disavow any knowledge of my actions.