For some basic exploration of a joined update, check out:

"Anatomy of a joined update" article in December 12, 2006 Centerfield
newsletter

For general join primer, check out:

"SQL Joins Primer" article in February 12, 2007 Centerfield newsletter

Here's the link:

http://www.centerfieldtechnology.com/publications/

Also, I think SystemiNews had an article on joins in their latest magazine
(ProVIP edition).

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Another non Series i SQL question

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)

Thanks,

Jeff Young


This thread ...

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