Re: Another non Series i SQL question

Unfortunately, I don't currently have the luxury of updating a file from a file.
The application does create a change file, however, which >might< work in this
instance. I hesitate because I dimly recall that some parts of that table are
not simple fields - thus data would need to be broken out of larger fields. This might actually work, provided the application does not put additional
information in the field locations. I will have to take a look. But, if the
application behaves in this instance, I would much prefer to use a table to
update a table, instead of either a list or one at a time.

Thanks.

John McKee

Quoting darren@xxxxxxxxx:

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