想在oracle sql中的MERGE语句中添加一些条件以进行插入/更新

I have to insert/update some RECORDS in table target_table. These records are coming one source_table.

我必须在表target_table中插入/更新一些RECORDS。这些记录来自一个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（val1'，'val2）

then only there should be update, else no update or insert.

然后只有更新，否则没有更新或插入。

3 个解决方案

#1

You can simply add WHERE clause 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
...

#2

you can use this article for your question by pinal Dava

您可以通过pinal Dava将此文章用于您的问题

MERGE statement

MERGE声明

#3

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);

#1