SQL 想在oracle sql中的MERGE语句中添加一些条件进行插入/更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14519933/
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
Want to add some conditions in MERGE statement in oracle sql for insert/update
提问by Bit_hunter
I have to insert/update some RECORDS in table target_table. These records are coming one source_table.
我必须在表 target_table 中插入/更新一些记录。这些记录来自一个 source_table。
I am using MERGE for update/insert in target_table. Query is as below
我在 target_table 中使用 MERGE 进行更新/插入。查询如下
MERGE
INTO target_table tgt
USING source_table src
ON ( src.column1 = tgt.column1 and
src.column2 = tgt.column2)
WHEN MATCHED
THEN
UPDATE
SET tgt.column3= src.column3,
tgt.column4 = src.coulmn4
WHEN NOT MATCHED
THEN
INSERT ( tgt.column1,
tgt.column2,
tgt.column3,
tgt.column4 )
VALUES ( src.coulmn1,
src.coulmn2,
src.coulmn3,
src.coulmn4);
I want to add some specific condition on update.
我想在更新时添加一些特定条件。
IF target_table.column3 in (val1','val2)
IF target_table.column3 in (val1','val2)
then only there should be update, else no update or insert.
那么只有应该有更新，否则没有更新或插入。
回答by psur
You can simply add WHEREclause to UPDATE. More about it in oracle docs.
您可以简单地将WHERE子句添加到UPDATE. 在 oracle 文档中有更多关于它的信息。
So in your case it should look like:
所以在你的情况下它应该是这样的：
...
WHEN MATCHED
THEN
UPDATE
SET tgt.column3= src.column3,
tgt.column4 = src.coulmn4
WHERE tgt.column3 IN (val1, val2)
WHEN NOT MATCHED
...
回答by A.Goutam
you can use this article for your question by pinal Dava
您可以使用 pinal Dava 的这篇文章回答您的问题
回答by Somashekhar Dhanashree
Instead Try Doing As below:
而是尝试执行以下操作：
MERGE
INTO target_table tgt
USING source_table src
ON (src.column1 = tgt.column1)
WHEN MATCHED
THEN
UPDATE
SET tgt.column3= src.column3,
tgt.column4 = src.coulmn4
WHERE src.column1 = tgt.column1 and
src.column2 = tgt.column2
WHEN NOT MATCHED
THEN
INSERT ( tgt.column1,
tgt.column2,
tgt.column3,
tgt.column4 )
VALUES ( src.coulmn1,
src.coulmn2,
src.coulmn3,
src.coulmn4);
