Re: Another non Series i SQL question

Normally you do have to look out for multiple records trying to be assigned
to the SET value, however, Sum(InvAmt), is being used, so multiple records
will not cause an error.





Joe Pluta
<joepluta@plutabr
others.com> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

03/27/2008 01:03 Subject
PM Re: Another non Series i SQL
question

Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






Jeff Young wrote:

When using the Where Exists in this context, do I need a separate Exists

clause for each join pair that I am using or can I place it in one
statement.

Ex. Update column Amount in table OpenAR with the total of column InvAmt

from table InvHdr when column Company in table OpenAR matches column
InvCmpny in table InvHdr and column Customer in table OpenAR matches column
InvCust in table InvHdr.

Which SQL statment would be correct:
1. Update OpenAR Set Amount = (Select Sum(InvAmt) from InvHdr where

Company = InvCmpny and Customer = InvCust)

Where Exists ((Select 1 from InvHdr where Company

= InvCmpny and Customer = InvCust)

2. Update OpenAR Set Amount = (Select Sum(InvAmt) from InvHdr where

Company = InvCmpny and Customer = InvCust)

Where Exists (Select 1 from InvHdr where Company =

InvCmpny) and Exists (Select 1 from InvHdr where Customer = InvCust)

In fact, you must place it in one statement. Think about it: the point
of the WHERE EXISTS is to make sure a record is in the file so that the
SET will work. So, the WHERE in the EXISTS much match the WHERE in the
SET. In your example number two, a row will be returned as long as
there is *any* record for the same company, regardless of the customer,
and *any* record for the customer, regardless of the company. So if you
were looking for company A, customer B, and you had records for company
A, customer Q and company Z, customer B, the WHERE EXISTS would work,
even though there was no record for company A, customer B.

One additional detail: the WHERE EXISTS checks to make sure at least one
record exists; if more than one record exists you will get an SQL
error. To remove that possibility, you'd need to check the count of
matching records.

Joe
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.