Path: utzoo!mnetor!uunet!husc6!think!ames!pasteur!postgres!larry
From: larry@postgres.uucp (Larry Rowe)
Newsgroups: comp.databases
Subject: Re: Quel vs SQL in Ingres 5.0
Message-ID: <2549@pasteur.Berkeley.Edu>
Date: 20 Apr 88 03:07:41 GMT
References: <4350@ihlpf.ATT.COM> <551@hscfvax.harvard.edu> <135@infmx.UUCP>
Sender: news@pasteur.Berkeley.Edu
Reply-To: larry@postgres.UUCP (Larry Rowe)
Organization: Postgres Research Group, UC Berkeley
Lines: 40
Keywords: quel, sql, ingres, informix, correlated subquery

In article <135@infmx.UUCP> aland@infmx.UUCP (Dr. Scump) writes:
>In article <551@hscfvax.harvard.edu>, pavlov@hscfvax.harvard.edu (G.Pavlov) writes:
>> 
>
>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:
>
>   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)  
>
This query is not valid according to the ANSI SQL standard (draft
report) dated Feb 1986.  The syntax for the update statement is

   <update statement: searched> ::=
	UPDATE <table name>
	SET <set clause: searched> [{, <set clause: searched> }]
	WHERE <search condition>

   <set clause: searched> ::=
	<object column: searched> = { <value expression> | NULL }

A <value expression> is a computed value that may contain constants,
references to other columns in the table being updated, or functions
of these values.

The feature you are using in INFORMIX SQL is a good one.  It should be
in every implementation of SQL and it should be added to the standard.
But today, at least as far as I can read it, it is not part of the
standard.  The reason is because of the direct update of tuples
implementation used in System-R.  You have to disallow references
to another table to avoid the halloween problem.
	larry