Allen Pitts
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_TE ST
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_TE ST TST
SET LOCATION_NAME = (SELECT LOCATION_NAME
FROM J3030_ACP_CHNG_LOC_NAME_TE MP 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
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_TE
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_TE
SET LOCATION_NAME = (SELECT LOCATION_NAME
FROM J3030_ACP_CHNG_LOC_NAME_TE
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Has to tweak it a bit but the MERGE approach is superior.
Open in new window
However, in your case you could just check against NULL, and replace with the original value:Open in new window