0

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%'
ramrah
  • 1
  • 1
  • Please, mention which database you are using. See https://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005/15766129 – Xypron Oct 11 '20 at 19:37

3 Answers3

2

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;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This works fine, but effectively updates *all rows* in the table even if the column value is not changed. This could be not desirable in case of a large table and a small number of changed rows. – Marmite Bomber Oct 14 '20 at 06:52
2

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
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
-1

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.

coderpc
  • 4,119
  • 6
  • 51
  • 93
Hardik Yewale
  • 340
  • 1
  • 8