SQL 在 CASE 语句中插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5996591/
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
INSERT INTO in a CASE statement
提问by Connection
I'm wondering if it's possible to have a INSERT INTO statement within a CASE statement in SQL code.
我想知道是否可以在 SQL 代码的 CASE 语句中包含 INSERT INTO 语句。
Here's a rough pseudocode of what I'm trying to do:
这是我正在尝试做的粗略伪代码：
SELECT (CASE (SELECT SomeValue FROM SomeTable)
WHEN NULL THEN
INSERT INTO OtherTable VALUES (1, 2, 3)
(SELECT NewlyInsertedValue FROM OtherTable)
ELSE
(SELECT SomeOtherValue FROM WeirdTable)
END),
Column1,
Column2
FROM BigTable
回答by mellamokb
You will need to accomplish with IF...THENstatements instead. Something roughly like this (not sure about syntax for db2):
您将需要使用IF...THEN语句来完成。大致是这样的（不确定 db2 的语法）：
SELECT @SomeValue = SomeValue FROM SomeTable
IF @SomeValue IS NULL
INSERT INTO OtherTable VALUES (1, 2, 3)
SELECT NewlyInsertedValue FROM OtherTable;
ELSE
INSERT INTO OtherTable VALUES (1, 2, 3)
SELECT SomeOtherValue FROM WeirdTable;
END IF;
回答by Conrad Frix
You could do it two statements like so.
你可以像这样做两个陈述。
First insert into otherwhen somevalue is null
首先插入other时somevalue is null
INSERT INTO othertable
SELECT 1,
2,
3
FROM bigtable
WHERE somevalue IS NULL;
Then left join to both tables on Somevaluebeing nullor not null
然后在Somevaluebenull或not null
SELECT Coalesce(othertable.newlyinsertedvalue, weirdtable.someothervalue) foo,
column1,
column2
FROM bigtable
LEFT OUTER JOIN othertable
ON somevalue IS NULL
LEFT OUTER JOIN weirdtable
ON somevalue IS NOT NULL
My guess is that you will actually have to modify the joins to be something like
我的猜测是，您实际上必须将连接修改为类似
LEFT OUTER JOIN othertable
ON somevalue IS NULL
and bigtable.id = othertable.id
LEFT OUTER JOIN weirdtable
ON somevalue IS NOT NULL
and bigtable.id = weirdtable .id
Note: I'm not sure what the DB2 equivalent of Coalesce is
注意：我不确定 Coalesce 的 DB2 等价物是什么
