This is the code I am trying to run in one statement for an oracle database:
Update INVN_SBS SET DESCRIPTION4='1' Where DCS_CODE LIKE '___ACH%'
Update INVN_SBS SET DESCRIPTION4='2' Where DCS_CODE LIKE '___ACO%'
Update INVN_SBS SET DESCRIPTION4='3' Where DCS_CODE LIKE '___ACS%'
Update INVN_SBS SET DESCRIPTION4='4' Where DCS_CODE LIKE '___ADJ%'
Update INVN_SBS SET DESCRIPTION4='5' Where DCS_CODE LIKE '___BAG%'
CASE helps in such cases, e.g.
update invn_sbs set
description4 = case when dcs_code like 'ACH%' then '1'
when dcs_code like 'AC0%' then '2'
etc.
else description4
end;
Yes, it is preferable to run the update only in one statement. Why?
Each UPDATE will require most probably a costly full table scan, so it is desired to not repeat it many times.
You need two thinks,
combine the value to be updated in a case statement and
combine the WHERE predicates to one expression using OR
The latter is important as you do not want to update all rows but only the selected once.
For your example is would be:
update INVN_SBS
set DESCRIPTION4 =
case
when DCS_CODE LIKE '___ACH%' then '1'
when DCS_CODE LIKE '___ACO%' then '2'
when DCS_CODE LIKE '___ACS%' then '3'
when DCS_CODE LIKE '___ADJ%' then '4'
when DCS_CODE LIKE '___BAG%' then '5' end
where DCS_CODE LIKE '___ACH%' or
DCS_CODE LIKE '___ACO%' or
DCS_CODE LIKE '___ACS%' or
DCS_CODE LIKE '___ADJ%' or
DCS_CODE LIKE '___BAG%';
The weak point is that you need to repeat twice very similar expression, that could lead to typo and wrong results if done manually.
One trick you can use is to deploy updatable views which limits the duplicated coding
update (
select DESCRIPTION4,DCS_CODE,
case
when DCS_CODE LIKE '___ACH%' then '1'
when DCS_CODE LIKE '___ACO%' then '2'
when DCS_CODE LIKE '___ACS%' then '3'
when DCS_CODE LIKE '___ADJ%' then '4'
when DCS_CODE LIKE '___BAG%' then '5' end as DESCRIPTION4_new
from INVN_SBS)
set DESCRIPTION4 = DESCRIPTION4_NEW
where DESCRIPTION4_NEW is not NULL
Why don't you create a PROCEDURE?
For Example :
CREATE OR REPLACE PROCEDURE ex
IS
BEGIN
-- The update sentence goes here
-- EXECUTE IMMEDIATE
-- 'UPDATE bar SET ...';
END;
You can test it with:
SET serveroutput ON;
exec ex ;
The PROCEDUREis stored on the database, so you can use it later on.
