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 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-2019 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].