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

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
<jmmckee@flinthil
ls.com> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

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


Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






This is a tedious update. I am wondering if there is a way to use SQL to
make
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
Surgery
staff. I am already thinking that the process is likely not efficient and
am
hoping for a safer method. But, my SQL skills are way toward the novice
side.

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

SELECT INVENTORY_ID, SYSTEM_ID FROM INVENTORY_HEADER
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:

UPDATE PREFERENCE_DETAIL
SET RESOURCE_ID = // system id from second SELECT above
WHERE RESOURCE_TYPE = 'INVENTORY' and
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
additional
columns that are tested before the change is allowed, specifically
SECTION_ID
and SUBSECTION_ID. It is very tedious to use the application when the
inventory items could be on multiple sections and/or subsections. Thus
SQL.

I am resigned to doing this via the two step method, as it is what I
understand.
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
being
done in weekly batches, for however long. Problem is that our corporate
office
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-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].