SQL Oracle MERGE - 如果不匹配则在条件通过时更新

Question

提问by BackSlash

I need to merge some values into a table, updating a field when a row with the specified key already exists, or inserting a new row if it doesn't exist.

我需要将一些值合并到一个表中，当具有指定键的行已经存在时更新一个字段，或者如果它不存在则插入一个新行。

This is my table:

这是我的表：

profiles(name, surname, active);

where:

在哪里：

name    VARCHAR2(30)
surname VARCHAR2(30)
active  NUMBER(1)

name and surname -> composite primary key

I'm using this query:

我正在使用这个查询：

MERGE INTO profiles USING (
    SELECT
        'Mark' myName,
        'Zibi' mySurname,
        '1'    myActive
   FROM DUAL
) ON (
   name = myName
   AND surname = mySurname
)
WHEN MATCHED THEN
    UPDATE SET
        active = myActive
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );

It works, but it updates a record even if activeis already set to 1.

它有效，但即使active已设置为，它也会更新记录1。

What I would like to do is something like this:

我想做的是这样的：

WHEN MATCHED THEN
    IF(active != myActive)
        UPDATE SET
            active = myActive
    ELSE
        RAISE CUSTOM EXCEPTION
WHEN NOT MATCHED THEN
    INSERT [...]

Is that possible? AFAIK I cannot put an iflike this into a MERGEstatement, so how could it be done?

那可能吗？AFAIK 我不能把if这样的东西放到MERGE声明中，那么怎么做呢？

Answer 1

回答by Richard Pascual

Using PL/SQL to Run a Conditional Merge Operation

使用 PL/SQL 运行条件合并操作

Edit:The original post asks how to process an existing set of data into an established table (named: PROFILES) through an approach that SQL or PL/SQL can solve it.
Edit Again:The last comment from OP was pretty subtle. If you don't have direct SQL access, then you will need a CURSOR, a driving query or some other construct to process each of the records your feeding in anyways. Many JDBC based middle-ware components also accept cursors as inputs. You could feed in all your data in one procedure call... take a look at REF CURSORdata types in PL/SQL. If that is the case, this solution can still help.

编辑：原帖询问如何通过 SQL 或 PL/SQL 可以解决的方法将现有数据集处理为已建立的表（名为：PROFILES）。
再次编辑：OP 的最后一条评论非常微妙。如果您没有直接的 SQL 访问权限，那么您将需要一个CURSOR驱动查询或其他一些结构来处理您输入的每条记录。许多基于 JDBC 的中间件组件也接受游标作为输入。您可以在一个过程调用中输入所有数据……看看REF CURSORPL/SQL 中的数据类型。如果是这种情况，此解决方案仍然可以提供帮助。

Using a composite join key, update data in a target table based on multiple criteria:

使用复合连接键，根据多个条件更新目标表中的数据：

INSERTsource data if it does not exist already.
Toggle or UPDATEa status value if the person identifier (name + surname) exists.
If person already exists in the target table and has an 'active' status already, skip it.

INSERT如果源数据不存在。
UPDATE如果人员标识符（姓名 + 姓氏）存在，则切换或状态值。
如果目标表中已经存在人员并且已经具有“活动”状态，则跳过它。

Sample Data

样本数据

I named my tables slightly different and modified the column name "name" which is a reserved sql/plsql keyword... to prevent any possible future conflicts.

我命名我的表略有不同，并修改了列名“name”，它是一个保留的 sql/plsql 关键字......以防止任何可能的未来冲突。

The sample data insert statements (DML):

示例数据插入语句 (DML)：

*For clarity: The names in the test schema are not an exact match to the OP. STACK_PROFILES= PROFILESand STACK_PROFILE_MERGE_SOURCErepresents "some source"... this could have been an xml feed, a csv text file, etc.etc.

*为清楚起见：测试模式中的名称与 OP 不完全匹配。STACK_PROFILES=PROFILES并STACK_PROFILE_MERGE_SOURCE代表“某些来源”……这可能是一个 xml 提要、一个 csv 文本文件等。

from: load_profile_data.sql...

CREATE TABLE "STACK_PROFILES"
   ( "PROFILE_NAME" VARCHAR2(40),
     "SURNAME" VARCHAR2(40),
     "ACTIVE" NUMBER(1,0),
   CONSTRAINT "STACK_PROFILES_PK" PRIMARY KEY ("PROFILE_NAME", "SURNAME") ENABLE
)
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('LOIS'  , 'LAINE', 0);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('MARTIN', 'SHORT', 1);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('ROBIN' , 'WILLIAMS', 0);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('GRACE' , 'HOPPER', 0);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('LOIS'  , 'LAINE-KENT', 0);

CREATE TABLE "STACK_PROFILES"
   ( "PROFILE_NAME" VARCHAR2(40),
     "SURNAME" VARCHAR2(40),
     "ACTIVE" NUMBER(1,0),
   CONSTRAINT "STACK_PROFILES_PK" PRIMARY KEY ("PROFILE_NAME", "SURNAME") ENABLE
)
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('LOIS'  , 'LAINE', 0);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('MARTIN', 'SHORT', 1);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('ROBIN' , 'WILLIAMS', 0);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('GRACE' , 'HOPPER', 0);
INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('LOIS'  , 'LAINE-KENT', 0);

commit; ...

CREATE TABLE "STACK_PROFILE_MERGE_SOURCE"
   ( "PROFILE_NAME" VARCHAR2(40),
     "SURNAME" VARCHAR2(40),
     CONSTRAINT "STACK_PROFILE_MERGE_SOURCE_PK" PRIMARY KEY ("PROFILE_NAME", "SURNAME")
     ENABLE
)
/

CREATE TABLE "STACK_PROFILE_MERGE_SOURCE"
   ( "PROFILE_NAME" VARCHAR2(40),
     "SURNAME" VARCHAR2(40),
     CONSTRAINT "STACK_PROFILE_MERGE_SOURCE_PK" PRIMARY KEY ("PROFILE_NAME", "SURNAME")
     ENABLE
)
/

INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('BRUCE' , 'WAYNE'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('SPONGE' , 'ROBERT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('CLARK' , 'KENT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('LOIS' , 'LAINE'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('MARTIN' , 'SHORT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('DAMON' , 'WAYANS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('ROBIN' , 'WILLIAMS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('BRUCE' , 'WILLIS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('DENNIS' , 'HOPPER'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('WHOOPI' , 'GOLDBERG'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('GRACE' , 'HOPPER'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('JERI' , 'RYAN');

Test Cases

测试用例

It's helpful to understand the requirements presented. Writing up a few test cases gets us closer.

了解所提出的要求很有帮助。编写一些测试用例让我们更接近。

For test cases 1 and 2...

对于测试用例 1 和 2...

Conditional Merge Test Cases 1,2

条件合并测试用例 1,2

For test cases 3 and 4...

对于测试用例 3 和 4...

Conditional Merge Test Cases 3,4

条件合并测试用例 3,4

The PL/SQL Source Code

PL/SQL 源代码

There is a simpler way to apply additional conditional logic through a SQL-merge like function. The PL/SQL Anonymous block following uses outer join syntaxto identify records to be inserted vs. updated. The third category (active and already present in the target table) is also observed as the cursor processing loop skips records of that definition.

有一种更简单的方法可以通过类似 SQL 合并的函数应用附加条件逻辑。下面的 PL/SQL Anonymous 块outer join syntax用于识别要插入和更新的记录。当游标处理循环跳过该定义的记录时，也会观察到第三类（活动的并且已经存在于目标表中）。

The processing loop and cursor

处理循环和游标

We use the FOR UPDATEand WHERE CURRENT OFsyntax in the dml operations because the state of data referenced within this query changes during the lifespan of its use.

我们在 dml 操作中使用FOR UPDATEandWHERE CURRENT OF语法，因为此查询中引用的数据状态在其使用期间会发生变化。

 declare
    c_default_status_active   constant number:= 1;
    c_status_inactive         constant number:= 0;

    cursor profile_cur is
       select sp.profile_name as target_name, 
              sp.surname as target_surname, sp.active as original_status,
              spm.profile_name as source_name, spm.surname as source_surname

         from stack_profiles sp, stack_profile_merge_source spm
        where spm.profile_name = sp.profile_name(+)
          and spm.surname = sp.surname(+)
        order by spm.profile_name asc nulls last, 
          spm.surname asc
          for update of sp.profile_name, sp.surname, sp.active;

        v_rec_profile  profile_cur%ROWTYPE;

     begin

      open profile_cur;
     fetch profile_cur into v_rec_profile;

     while profile_cur%found loop
       -- insert condition (no match in outer join...)
       if v_rec_profile.original_status is null
       then
       insert into stack_profiles (profile_name, surname, active)
       values (v_rec_profile.source_name, v_rec_profile.source_surname, 
           c_default_status_active);

       elsif
       -- flip status from inactive to active for existing but 
       -- inactive records.
       v_rec_profile.original_status = c_status_inactive then
       update stack_profiles
          set active = c_default_status_active
        where current of profile_cur;
          end if;

      fetch profile_cur into v_rec_profile;
      end loop;
      close profile_cur;

commit;

end;

Discussion

讨论

I have noted many different approaches to this type of problem. The specific approach used here is to demonstrate the concept involved. Results may vary depending on the database configuration, its usage and set up.

我已经注意到解决此类问题的许多不同方法。这里使用的具体方法是演示所涉及的概念。结果可能因数据库配置、使用情况和设置而异。

Answer 2

回答by Mikhail

Ok, this is not good practice, i suppose, but since your ACTIVE column has type NUMBER(1) you can easily generate ORA-01438 exception by simply trying to update it's value to bigger one. For example, something like this will throw an exception, if new and old values of active are equal:

好的，我想这不是一个好习惯，但是由于您的 ACTIVE 列的类型为 NUMBER(1)，您可以通过简单地尝试将其值更新为更大的值来轻松生成 ORA-01438 异常。例如，如果 active 的新旧值相等，这样的事情将引发异常：

MERGE INTO profiles USING (
    SELECT
        'Mark' myName,
        'Zibi' mySurname,
        1    myActive
   FROM DUAL
) ON (
   name = myName
   AND surname = mySurname
)
WHEN MATCHED THEN
    UPDATE SET
        active =  CASE WHEN active = myActive THEN 11 ELSE myActive END
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );

Answer 3

回答by ThinkJet

It's better to use PL/SQL in such a case via the stored procedure or just by executing anonymous SQL block from a client side instead of single MERGESQL statement.

在这种情况下，最好通过存储过程或仅通过从客户端执行匿名 SQL 块而不是单个MERGESQL 语句来使用 PL/SQL 。

Anonymous PL/SQL block may look like:

匿名 PL/SQL 块可能如下所示：

declare
  -- Parameters of query, initialization values  
  pName    profiles.name%type    := 'Mark';
  pSurname profiles.surname%type := 'Zibi';
  pActive  profiles.active%type  := 0;

  -- variable used for test against table
  vIsActiveInDb profiles.active%type;
begin

  select 
    max(profs.active) into vIsActiveInDb
  from 
    profiles profs
  where 
    profs.name = pName and profs.surname = pSurname
  ;

  if(vIsActiveInDb is null) then
    -- profile not found, create new one 
    insert into profiles(name, surname, active)
    values(pName, pSurname, pActive);

  elsif(vIsActiveInDb != pActive) then
    -- profile found, activity flag differs 
    update profiles set active = pActive 
    where name = pName and surname = pSurname;

  else
    -- profile found with same activity flag
    raise_application_error(
      -20001, -- custom error code from -20000 to -20999
      'Profile "'||pName||' '||pSurname||'" already exists with same activity flag'
    );  
  end if;

end;

SQLFiddle

There are two suggestions in code above:
1. (name, surname)pair is a primary key, so always selected single row or nothing;
2. activefield can't be null (e.g. created with not nullconstraint).
Code would be a little bit more complicated if this suggestions fails. This variant may be found in this SQLFiddle.

有在代码两个建议以上：
1.(name, surname)对是一主键，所以总是选择单列或不存在;
2.active字段不能为空（例如用not null约束创建）。
如果此建议失败，代码会稍微复杂一些。此变体可以在this SQLFiddle.

I never used MyBatisbut based on answer from your commentXML description for such query may look like that:

我从未使用过，MyBatis但根据您对此类查询的评论XML 描述的回答可能如下所示：

<update id="UpdateProfileActivity" parameterType="map" statementType="CALLABLE">   
  declare
    -- Parameters of query, initialization values
    pName    profiles.name%type    := #{piName,    mode=IN, jdbcType=VARCHAR};
    pSurname profiles.surname%type := #{piSurname, mode=IN, jdbcType=VARCHAR};
    pActive  profiles.active%type  := #{piActivity,mode=IN, jdbcType=NUMERIC};

    -- variable used for test against table
    vIsActiveInDb profiles.active%type;   begin

    select
      max(profs.active) into vIsActiveInDb
    from
      profiles profs
    where
      profs.name = pName and profs.surname = pSurname
    ;

    if(vIsActiveInDb is null) then
      -- profile not found, create new one
      insert into profiles(name, surname, active)
      values(pName, pSurname, pActive);

    elsif(vIsActiveInDb != pActive) then
      -- profile found, activity flag differs
      update profiles set active = pActive
      where name = pName and surname = pSurname;

    else
      -- profile found with same activity flag
      raise_application_error(
        -20001, -- custom error code from -20000 to -20999
        'Profile "'||pName||' '||pSurname||'" already exists with same activity flag'
      );
    end if;

  end; 
</update>

Answer 4

回答by Mohamed.Abdo

You can do this by adding wherecondition either on source -using (---- subquery ---), to filter when matched command, or add where condition after when not matched.

您可以通过where在 source -using (---- subquery ---) 上添加条件来执行此操作，以在匹配命令时进行过滤，或者在不匹配时添加 where 条件。

In the following example, I will merge records from id 520 up to 530, at the same time I will not insert record where id =525

在下面的例子中，我将从 id 520 到 530 的记录合并，同时我不会插入 id = 525 的记录

--------
merge into merchant_tmp2 dest
using (select * from merchant where id between 520 and 530) src
on(dest.id=src.id)
when matched then 
update set address=address ||' - updated'
when not matched then 
insert (ID,....)
values (src.ID,....)
where src.id <> 525;

ref:https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

参考：https: //docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

SQL Oracle MERGE - 如果不匹配则在条件通过时更新

提问by BackSlash

回答by Richard Pascual

Using PL/SQL to Run a Conditional Merge Operation

使用 PL/SQL 运行条件合并操作

Sample Data

样本数据

Test Cases

测试用例

The PL/SQL Source Code

PL/SQL 源代码

Discussion

讨论

回答by Mikhail

回答by ThinkJet

回答by Mohamed.Abdo

相关推荐

最近更新

标签

SQL Oracle MERGE - 如果不匹配则在条件通过时更新

提问by BackSlash

回答by Richard Pascual

Using PL/SQL to Run a Conditional Merge Operation

使用 PL/SQL 运行条件合并操作

Sample Data

样本数据

Test Cases

测试用例

The PL/SQL Source Code

PL/SQL 源代码

Discussion

讨论

回答by Mikhail

回答by ThinkJet

回答by Mohamed.Abdo

相关推荐

Oracle SQL：如何在给定列名的情况下找到表名？

SQL 条件唯一约束

IO 错误：网络适配器无法与 Oracle 11gR2 建立连接。与 SQL 开发人员联系

在 sql server 数据库中找到可用空间的最佳方法？

相关推荐

最近更新

标签