SQL 使用 CASE 和 IN 更新 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5170864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE with CASE and IN - Oracle
提问by user641605
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 Server 中的作用就像一个魅力。不幸的是，它需要在 Oracle 数据库上运行。我一直在网上搜索有关如何转换它的解决方案，但没有任何成功：/
The query looks like this i SQL:
查询看起来像这样 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_gr1and 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.
我的问题还在于列budgetpost_gr1和预算邮政是字母数字，甲骨文似乎希望将列表视为数字。该列表是预定义为逗号分隔列表的变量/参数，它只是转储到查询中。
采纳答案by user641605
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
得到了一个可以运行的解决方案。不知道它是否是最佳的。我所做的是根据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;
使用：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):
所以我的最终代码看起来像这样（$bp_gr1'是像这样的字符串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?
有没有办法让它运行得更快？
回答by Thomas
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).
你说budgetpost 是字母数字。这意味着它正在寻找与字符串的比较。您应该尝试将参数用单引号括起来（并且您缺少 Case 表达式中的最后一个 THEN）。
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
回答by Andomar
Use to_numberto convert budgpostto a number:
使用to_number转换budgpost为数字：
when to_number(budgpost,99999) in (1001,1012,50055) THEN 'BP_GR_A'
EDIT: Make sure there are enough 9's in to_numberto match to largest budget post.
编辑：确保有足够的9's into_number匹配最大的预算帖子。
If there are non-numeric budget posts, you could filter them out with a whereclause at then end of the query:
如果有非数字预算帖子，您可以where在查询结束时使用子句过滤掉它们：
where regexp_like(budgpost, '^-?[[:digit:],.]+$')
回答by Gary Myers
"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'
回答by Scott
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".
您可以使用另一种解决方法来使用联接进行更新。下面的示例假设您希望通过包含查找值（在本例中将用户名存储在表中）来对表进行反规范化。更新包括用于查找名称的连接，并且在支持找到或未找到名称的 CASE 语句中评估输出。完成这项工作的关键是确保从连接中出来的所有列都有唯一的名称。在示例代码中，请注意 b.user_name 如何与 a.user_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;
