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

Follow-Ups:

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