Oracle 中的条件更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18536818/
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
Conditional update statement in Oracle
提问by Dimuthu
I have a problem with building a conditional update statement in Oracle. For understandability, I'll simplify the problem and basically my update statement should look like this:
我在Oracle 中构建条件更新语句时遇到问题。为了便于理解，我将简化问题，基本上我的更新语句应如下所示：
UPDATE SAMPLE_TAB1 t
SET t.sample_column1 =NVL(t.sample_column1, **SOME LOGIC**);
The ***SOME LOGIC***part should look like this: (Please consider this is just a pseudo code)
该***SOME LOGIC***部分应如下所示：（请考虑这只是一个伪代码）
IF ((SELECT sample_column2 FROM SAMPLE_TAB2
WHERE sample_column2= sample_value2
AND sample_column3 = sample_value3 )='FALSE' THEN
t.sample_column1 =0;
ELSE
t.sample_column1 =(SELECT sample_column1 FROM SAMPLE_TAB3
WHERE sample_column4= sample_value4
AND sample_column5 = sample_value5 )
END IF;
Any kind of thoughts on this problem would be welcome. Thank you.
欢迎对这个问题提出任何想法。谢谢你。
回答by Jasti
Try the following code
试试下面的代码
UPDATE SAMPLE_TAB1 t
SET t.sample_column1 = (
case when ((SELECT sample_column2 FROM SAMPLE_TAB2
WHERE sample_column2= sample_value2
AND sample_column3 = sample_value3 ) = 'FALSE' )
then 0
else
(SELECT sample_column1 FROM SAMPLE_TAB3
WHERE sample_column4= sample_value4
AND sample_column5 = sample_value5 )
end
)
WHERE t.sample_column1 is not null;
回答by schurik
try the following
尝试以下
UPDATE SAMPLE_TAB1 t
SET t.sample_column1 = NVL( (SELECT sample_column2 FROM ...), 0)
WHERE t.sample_column1 is not null
;
