使用 CASE 和 IN 进行更新 - Oracle

2022-01-17 00:00:00 sql-update sql oracle

我写了一个查询，它在 SQL Server 中就像一个魅力.不幸的是，它需要在 Oracle 数据库上运行.我一直在网上搜索如何转换它的解决方案，但没有任何成功:/

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 :/

查询在 SQL 中如下所示:

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`

我的问题还在于列 budgetpost_gr1 和 budgetpost 是字母数字，Oracle 似乎希望将列表视为数字.该列表是预定义为逗号分隔列表的变量/参数，它只是转储到查询中.

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.

推荐答案

得到了一个可以运行的解决方案.不知道它是否是最优的.我所做的是根据 http://blogs.oracle.com 拆分字符串/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

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

使用:
select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual
通过 regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) 连接不为空；

所以我的最终代码如下所示($bp_gr1' 是字符串，如 1,2,3):

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?

相关文章