UPDATE with CASE and IN - Oracle

Question

I wrote a query that works like a charm in SQL Server. Unfortunately it needs to be run on an Oracle db. I have been searching the web inside out for a solution on how to convert it, without any success :/

The query looks like this i SQL:

UPDATE tab1   SET budgpost_gr1=     
CASE  WHEN (budgpost in (1001,1012,50055))  THEN 'BP_GR_A'   
      WHEN (budgpost in (5,10,98,0))  THEN 'BP_GR_B'  
      WHEN (budgpost in (11,876,7976,67465))     
      ELSE 'Missing' END`

My problem is also that the columns budgetpost_gr1 and budgetpost is alphanumeric and Oracle seems to want to see the list as numbers. The list are variables/parameters that is pre-defined as comma separated lists, which is just dumped into the query.

What *exactly* is your problem? Do you get an error? (if yes, post it). Does it update the wrong rows? And what is the datatype of budgetpost (note "alphanumeric" is **not** a datatype) — , Mar 02 '11 at 17:14
Sorry for beeing so inaccurate. The data type is Char. My bad. My problem is exactly what Adomar and Thomas is disussing below. — user641605, Mar 03 '11 at 07:25
That update should work just fine in Oracle too. Do you get an error? — Tony Andrews, Mar 02 '11 at 17:13

Thomas · Answer 1 · 2013-10-29T15:00:04.270

40

You said that budgetpost is alphanumeric. That means it is looking for comparisons against strings. You should try enclosing your parameters in single quotes (and you are missing the final THEN in the Case expression).

UPDATE tab1   
SET budgpost_gr1=   CASE  
                        WHEN (budgpost in ('1001','1012','50055'))  THEN 'BP_GR_A'   
                        WHEN (budgpost in ('5','10','98','0'))  THEN 'BP_GR_B'  
                        WHEN (budgpost in ('11','876','7976','67465')) THEN 'What?'
                        ELSE 'Missing' 
                        END

edited Oct 29 '13 at 15:00

answered Mar 02 '11 at 17:28

Thomas

63,911
12
95
141

That would work, but the question seems to suggest that the OP has no control over the `in` string – Andomar Mar 02 '11 at 17:40
@Andomar - Presumably, the OP has control over the case expression (otherwise, how can they possibly fix it?). If that's the case, then it seems logical that they would have the ability to reformat the In clauses before they get put into the Case expression. If budgpost is really a character column as is stated, then we have to assume that a non-numeric value might make its way into one of the In clauses. – Thomas Mar 02 '11 at 17:44
@Thomas: It's not uncommon for a DBA to have no control over the arguments passed by clients. In this case the client might pass `1001,1012,5055` or something. (Not sure, the question can be interpreted either way.) – Andomar Mar 02 '11 at 17:56
@Andomar - But then how would they fix the Case expression? If they can change the Case expression, then they should be able to correct the values passed to the Case expression even if they cannot change which values are passed to it. – Thomas Mar 02 '11 at 18:08
@Andomar - Said another way, if I'm only able to correct the procedure which is passed an argument with values like `1,2,3`. I can't change what I get passed, but I can take what I'm given and change it to '1','2','3'. – Thomas Mar 02 '11 at 18:10
@Thomas: Well then your answer could include a transformation from `1,2,3` to `'1','2','2'`; I think it's easier to cast the left-hand argument in that case! – Andomar Mar 02 '11 at 19:37
@Andomar - Granted. We're told that budgpost is alphanumeric but is it really? Is it just that the data type on the column is wrong? If so, your solution is clearly easier (or they could simply correct the data type). – Thomas Mar 02 '11 at 22:41
@Thomas @Andomar - You guys are right. This is what I been struggling with. I changed budgetposts datatype but it creates errors on other parts of the script. So I tried to transform the string from `1,2,3` to `'1',2','3'` with `select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null; ` – user641605 Mar 03 '11 at 07:30
@user641605 - Are you *building* a Case expression (i.e., building the SQL statement) or are you trying to drop something into an existing Case expression (e.g. `When budgpost In (@foo)`)? If the later, it will not work. You need a split function that will convert your delimited string into a table. You would then change the Case expression to be something like `When budgpost In(Select Value From SplitFunc(@myvar))`. – Thomas Mar 03 '11 at 16:36

Andomar · Answer 2 · 2011-03-03T07:31:38.360

3

Use to_number to convert budgpost to a number:

when to_number(budgpost,99999) in (1001,1012,50055) THEN 'BP_GR_A'

EDIT: Make sure there are enough 9's in to_number to match to largest budget post.

If there are non-numeric budget posts, you could filter them out with a where clause at then end of the query:

where regexp_like(budgpost, '^-?[[:digit:],.]+$')

edited Mar 03 '11 at 07:31

answered Mar 02 '11 at 17:25

Andomar

232,371
49
380
404

1

@user641605: Sounds like some budget posts are non-nummeric... answer edited to filter them out. If you can change the `in` string, use Thomas' answer – Andomar Mar 03 '11 at 07:33

score 2 · Accepted Answer · edited Feb 26 '14 at 12:14

Got a solution that runs. Don't know if it is optimal though. What I do is to split the string according to http://blogs.oracle.com/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

Using:
select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

So my final code looks like this ($bp_gr1' are strings like 1,2,3):

UPDATE TAB1
SET    BUDGPOST_GR1 =
          CASE
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR1'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR2'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR3'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR4'
             ELSE
                'SAKNAR BUDGETGRUPP'
          END;

Is there a way to make it run faster?

This query is overly complex and hard to understand. Looks like you're making Oracle process string inputs inside a shell script. You should process the inputs before plugging them into the SQL. That would simplify the query — roblogic, Oct 28 '15 at 01:47
I think that you can avoid the SELECTs and use REGEX with a LIKE. — Rafa Barragan, Nov 01 '21 at 07:41

score 1 · Answer 4 · answered Mar 02 '11 at 22:21

"The list are variables/paramaters that is pre-defined as comma separated lists". Do you mean that your query is actually

UPDATE tab1   SET budgpost_gr1=     
CASE  WHEN (budgpost in ('1001,1012,50055'))  THEN 'BP_GR_A'   
      WHEN (budgpost in ('5,10,98,0'))  THEN 'BP_GR_B'  
      WHEN (budgpost in ('11,876,7976,67465'))     
      ELSE 'Missing' END`

If so, you need a function to take a string and parse it into a list of numbers.

create type tab_num is table of number;

create or replace function f_str_to_nums (i_str in varchar2) return tab_num is
  v_tab_num tab_num := tab_num();
  v_start   number := 1;
  v_end     number;
  v_delim   VARCHAR2(1) := ',';
  v_cnt     number(1) := 1;
begin
  v_end := instr(i_str||v_delim,v_delim,1, v_start);
  WHILE v_end > 0 LOOP
    v_cnt := v_cnt + 1;
    v_tab_num.extend;
    v_tab_num(v_tab_num.count) := 
                  substr(i_str,v_start,v_end-v_start);
    v_start := v_end + 1;
    v_end := instr(i_str||v_delim,v_delim,v_start);
  END LOOP;
  RETURN v_tab_num;
end;
/

Then you can use the function like so:

select column_id, 
   case when column_id in 
     (select column_value from table(f_str_to_nums('1,2,3,4'))) then 'red' 
   else 'blue' end
from  user_tab_columns
where table_name = 'EMP'

score 0 · Answer 5 · answered Feb 07 '14 at 16:18

There is another workaround you can use to update using a join. This example below assumes you want to de-normalize a table by including a lookup value (in this case storing a users name in the table). The update includes a join to find the name and the output is evaluated in a CASE statement that supports the name being found or not found. The key to making this work is ensuring all the columns coming out of the join have unique names. In the sample code, notice how b.user_name conflicts with the a.user_name column and must be aliased with the unique name "user_user_name".

UPDATE
(
    SELECT a.user_id, a.user_name, b.user_name as user_user_name
    FROM some_table a
    LEFT OUTER JOIN user_table b ON a.user_id = b.user_id
    WHERE a.user_id IS NOT NULL
)
SET user_name = CASE
    WHEN user_user_name IS NOT NULL THEN user_user_name
    ELSE 'UNKNOWN'
    END;

UPDATE with CASE and IN - Oracle

5 Answers5

Linked