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.




This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].