PL/SQL Update Location Names

Allen Pitts
Allen Pitts used Ask the Experts™
on
Hello Expert,

Because of billing problems the business has asked that some Location Names
be changed in the prod database. Example copied herewith below shows
three name changes. Scope of request is 2400 names.

Created a test table which is mirror of Prod called J3030_ACP_CHNG_LOC_NAME_TEST
Created a temp table which has the new names and LOCATION_IDs associated with the new names.

Wrote query
UPDATE J3030_ACP_CHNG_LOC_NAME_TEST TST
SET LOCATION_NAME = (SELECT LOCATION_NAME
                 FROM J3030_ACP_CHNG_LOC_NAME_TEMP TEMP
                 WHERE TEMP.LOCATION_ID = TST.LOCATION_ID);

This works and changes names supplied in ....TEMP to
the correct new names at..TEST.

But for names where the script does not find a LOCATION_ID
it change the LOCATION_NAME to null.

How to alter the script so it only changes the LOCATION_NAME
in TEST where there is LOCATION_ID in TEMP?

Thanks.

Allen in Dallas

 
Example of Name Changes
 Existing LOCATION_NAME                                   New LOCATION_NAME
 Compass Grp/Bon Appetit-AU ChickFilA             CMPS GRP/Bon Appetit-AU ChickFilA
 Compass Grp/Canteen-Sterling Vend Spare 4       CMPS GRP/Canteen-Sterling Vend Spare 4
 Compass Grp/Bon Appetit-AU Admin                     CMPS GRP/Bon Appetit-AU Admin
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Use MERGE.

This is untested but should be close:
merge into J3030_ACP_CHNG_LOC_NAME_TEST tst
using ( SELECT location_id, LOCATION_NAME
                  FROM J3030_ACP_CHNG_LOC_NAME_TEMP
) tmp
on (tst.location_id=tmp.location_id)
when matched then update set tst.location_name=tmp_location_name
/
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
The traditional way is to add a where clause to the update replicating the select where clause in some way.
UPDATE J3030_ACP_CHNG_LOC_NAME_TEST TST
SET LOCATION_NAME = (SELECT LOCATION_NAME
                 FROM J3030_ACP_CHNG_LOC_NAME_TEMP TEMP
                 WHERE TEMP.LOCATION_ID = TST.LOCATION_ID)
where location_id in (select location_id from  J3030_ACP_CHNG_LOC_NAME_TEMP)

Open in new window

However, in your case you could just check against NULL, and replace with the original value:
UPDATE J3030_ACP_CHNG_LOC_NAME_TEST TST
SET LOCATION_NAME = nvl((SELECT LOCATION_NAME
                 FROM J3030_ACP_CHNG_LOC_NAME_TEMP TEMP
                 WHERE TEMP.LOCATION_ID = TST.LOCATION_ID), LOCATION_NAME)

Open in new window

Allen PittsBusiness analyst

Author

Commented:
Has to tweak it a bit but the MERGE approach is superior.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial