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)


Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3

----- Original Message ----
From: "darren@xxxxxxxxx" <darren@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, March 27, 2008 12:08:00 PM
Subject: Re: Another non Series i SQL question

The concept of updating a file using data from another file is a common SQL

The syntax is something like this:

update lib/file a set fld=
(select fld2 from lib/file2 b where a.key=b.key)
where exists
(select fld2 from lib/file2 b where a.key=b.key)

Is that enough to adapt it to your application? The concept is that you're
setting a value equal to a selection from another file, and then 'where
exists' makes sure that the selection value doesn't come back null, in case
you don't have a one-one relationship of data records in the source file.

John McKee
ls.com> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>

03/27/2008 11:23 Subject
AM Another non Series i SQL question

Please respond to
Midrange Systems

This is a tedious update. I am wondering if there is a way to use SQL to
the task less tedious and, especially, less error prone.

The application provides a method to do this table maintenance, but the
application is flawed, thus, I have been asked to do this to help the
staff. I am already thinking that the process is likely not efficient and
hoping for a safer method. But, my SQL skills are way toward the novice

Here is the mess: I have been given a list of inventory ids that need to
replaced. Sounds simple enough, a simple UPDATE. But, the table I need to
update does not contain the inventory id. Instead, it contains an
generated system id. So, the process goes like this:

WHERE INVENTORY_ID IN 9'',''.'','',''.......)

Write down the SYSTEM_ID next to the INVENTORY_ID.

Repeat the above SELECT for the replacement inventory ids.

Then, finally, do this:

SET RESOURCE_ID = // system id from second SELECT above
RESOURCE_ID = // system id from first select above

Is there a way to combine the two SELECTs with the UPDATE in a way that
does not
hose the tables? Or, is just doing this one at a time the safest, most
efficient path?

BTW, the reason the application won't work for this is that there are
columns that are tested before the change is allowed, specifically
and SUBSECTION_ID. It is very tedious to use the application when the
inventory items could be on multiple sections and/or subsections. Thus

I am resigned to doing this via the two step method, as it is what I
On the other hand, if there is a way to do this in a single step that is a
little less likely to mess things up, I would appreciate doing it that way.

Apparently, this will not be a one time issue. They are planning on this
done in weekly batches, for however long. Problem is that our corporate
likes to change things, regularly. Each inventory id is associated with a
specific vendor, thus the mess.

Comments are appreciated.

John McKee

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