Re: Another non Series i SQL question

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