想要在oracle sql的MERGE语句中添加一些条件以进行插入/更新 [英] Want to add some conditions in MERGE statement in oracle sql for insert/update

问题描述

我必须在表target_table中插入/更新一些记录.这些记录是一个source_table.

我正在使用MERGE进行更新/插入到target_table中.查询如下

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

我想在更新时添加一些特定条件.

IF target_table.column3 in (val1','val2)

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

解决方案

您可以简单地将WHERE子句添加到UPDATE. 有关oracle文档的更多信息.

因此，在您的情况下，其外观应为:

...
WHEN MATCHED
THEN
   UPDATE
   SET   tgt.column3= src.column3,
         tgt.column4 = src.coulmn4
   WHERE tgt.column3 IN (val1, val2) 
WHEN NOT MATCHED
...

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

I am using MERGE for update/insert in target_table. Query is as below

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)

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

解决方案

You can simply add WHERE clause to UPDATE. More about it in oracle docs.

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
...

这篇关于想要在oracle sql的MERGE语句中添加一些条件以进行插入/更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！