Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!mnetor!uunet!seismo!mimsy!oddjob!gargoyle!jpusa1!news
From: news@jpusa1.UUCP (usenet)
Newsgroups: comp.databases
Subject: RE:SQL query
Message-ID: <315@jpusa1.UUCP>
Date: Fri, 14-Aug-87 18:45:44 EDT
Article-I.D.: jpusa1.315
Posted: Fri Aug 14 18:45:44 1987
Date-Received: Sun, 16-Aug-87 06:21:18 EDT
Reply-To: rick@jpusa1.UUCP (Rick Mills)
Organization: JPUSA - Chicago, IL
Lines: 49

>From: barker@ems.MN.ORG (Bob W. Barker)
>Subject: SQL help
>
>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'/
>
>-Bob Barker
>
>
 I've found working with SQL in both popular micro database
management systems an adventure in semantics. 
 First let me give you an example of what we have had to do
when updating one table from another in a similar situation.
( NOTE: This is Informix syntax. )
	update cusstat
		set balance = balance +
			(select A_dollars from adjust where 
			 cusstat.name = adjust.name)
		where cusstat.name in 
			(select name from adjust where 
			 A_posted = "N");
 The "set" you wish to work on must be qualified by the outer
filter (where cusstat name in) before you begin the update.

 As one other gentleman pointed out - there can be no duplicate
names in the adjust table, as the subquery will return them all,
which of course will not be accepted by the set statement.(Errors!)

 Secondly - This is a prime example of the drawbacks of working
with SQL to do (basically) procedural data manipulation. Has
anybody else experienced a non-procedural crunch like this? What
method have you chosen to take? 

 I've suspected Query data-sub-languages are for just that - queries. 
The DML extensions to SQL are forced to work with domains qualified
by filters rather than individual records, which can be cumbersome
on a small machine with a large database. 

 Any one have similar questions or answers? I am very new to working
with databases and then mostly with micros, so I'm intrigued by
simple discussions....

		-Rick Mills
Rick Mills                                            JPUSA
{gargoyle,ihnp4}!jpusa1!rick                          (312) 561-2450