Link to home
Start Free TrialLog in
Avatar of Allen Pitts
Allen PittsFlag for United States of America

asked on

PL/SQL Update Location Names

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Allen Pitts

ASKER

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