Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!mnetor!uunet!husc6!rutgers!mtune!codas!cpsc6a!rtech!eric
From: eric@rtech.UUCP (Eric Lundblad)
Newsgroups: comp.databases
Subject: Re: SQL help
Message-ID: <1135@rtech.UUCP>
Date: Thu, 13-Aug-87 02:59:03 EDT
Article-I.D.: rtech.1135
Posted: Thu Aug 13 02:59:03 1987
Date-Received: Sat, 15-Aug-87 09:37:56 EDT
References: <464@ems.MN.ORG>
Organization: Relational Technology Inc. Alameda, CA 94501
Lines: 48

From article <464@ems.MN.ORG>, by barker@ems.MN.ORG (Bob W. Barker):
> I'm trying to whip up an SQL that
> will update the cusstat record of each customer that has corresponding 
> adjustment records where the posted field = N (for NO).  I'd also like to
> change the posted field to 'Y' after doing this but this isn't a real problem
> so I won't address it here.
> 
> 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.  

	I believe that you want to execute something like:

update cusstat
	set CS_balance = 
		(select CS_balance + A_dollars from adjust
		 where CS_custname = A_custname and A_posted = 'N')

	The difference here is that cusstat is not referenced on the from
list of the subselect. The cusstat fields that are used in the subselect
(CS_balance and CS_custname) would be correlated values to the outer
context (ie. the update statement). In other words, for each row in
cusstat, the subselect is executed using the current correlated values.
If the subselect returns zero rows, then the current cusstat row isn't updated
and the process in continued with the next row. If the subselect returns
one value, then you change the cusstat row and go on to the next. The $64
question is: what happens when the subselect returns more than one values?
Does the DBMS generate an error? Use the first value? The last value? All
of them?

	Note that I'm not an expert on the supported syntax for Informix
or Unify (I can't remember which one was referred to in the original
article). However, assuming a moderate level of orthoginality (something
that SQL isn't noted for) the above query should work. 

		eric

P.S. Damn, how do you spell orthoginality and where's my dictionary?

-- 

			Eric Lundblad
		   ucbvax!mtxinu!rtech!eric