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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

[sql] UPDATE with CASE and IN - Oracle

Examples related to sql

Examples related to oracle

Examples related to sql-update