Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!mnetor!uunet!seismo!columbia!rutgers!ucla-cs!zen!xanadu!larry
From: larry@xanadu.uucp (Larry Rowe)
Newsgroups: comp.databases
Subject: Re: SQL help
Message-ID: <3318@zen.berkeley.edu>
Date: Tue, 18-Aug-87 11:03:33 EDT
Article-I.D.: zen.3318
Posted: Tue Aug 18 11:03:33 1987
Date-Received: Thu, 20-Aug-87 03:11:42 EDT
References: <464@ems.MN.ORG>
Sender: news@zen.berkeley.edu
Reply-To: larry@xanadu.UUCP (Larry Rowe)
Organization: Postgres Research Group, UC Berkeley
Lines: 41
Keywords: SQL, Unify

In article <464@ems.MN.ORG> barker@ems.MN.ORG (Bob W. Barker) writes:
> .....
>
>This SQL doesn't quite work:
>
>update cusstat
>set CS_balance =
>	select CS_balance + A_dollars from cusstat, adjust
>	where CS_custname = A_custname and A_posted = 'N'/
>
>It updates the balances of all customers in cusstat.  Now, I imagine I need
>a 'where' clause to match the update (ie. update cusstat where CS_custname =
>A_custname or something) but I can't for the life of me get the syntax right!

SORRY FOLKS, this query is not expressable in SQL.  the left-hand side of
a set stmt in an update can be an expression involving constants and
refereences to attributes in the same record.  i believe this limitation
is in the langauge to avoid something known as the ``halloween problem''
where the order of scanning the table to be updated can change the
semantics of the query.  this arises because System-R (the folks who
brought you SQL) does direct update (i.e., when scanning the table to be
updated, the value is updated in the data page).

QUEL allows this query to be written, as follows:

	replace cusstat(CS_balance=CS_balance+adjust.A_dollars)
	where cusstat.CS_balance=adjust.A_custname
	      and adjust.A_posted='N'

you've hit on one of the reasons folks say that QUEL is a better
query language than SQL.

i expect that all SQL vendors will extend their products with a
construct to handle this case.  however, it may take awhile because
the implementation consequences are serious.  btw, INGRES uses deferred
update so the semantics can't depend on the scan order.

interesting point about the way you write queries.  you use implicit
tuple variables (i.e., CS_balance refers implicitly to the cusstat table)
but then you encode a tuple variable into the attribute name (i.e., CS_).
	larry