Forum Stats

  • 3,837,530 Users
  • 2,262,268 Discussions
  • 7,900,315 Comments

Discussions

How does Oracle handle this MERGE use case.

User_0JAQ3
User_0JAQ3 Member Posts: 15 Red Ribbon

Below is the exact use case that I need to resolve, but I understand that I can't use the statement below.

WHEN NOT MATCHED BY SOURCE

So how does Oracle handle the issue below in a merge statement?

--When there is a row that exists in target and same record does not exist in source then delete this record target

  

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,219 Red Diamond

    Hi, @User_0JAQ3

    -When there is a row that exists in target and same record does not exist in source then delete this record target

    1. In the USING clause, add a column that tells whether the row exists in the target table or not. (You may need to add an EXISTS or IN sub-query to the USING clause, or make it an outer join with the source and target tables)
    2. Add DELETE WHERE <condition> to the WHEN MATCHED THEN UPDATE clause

    For an example, see: Delta load logic using oracle merge/delete sql statement — oracle-tech

    If you'd care to post CREATE ABLE and INSERT statements for your tables (as they exist before the MERGE) and the desired results (what the target tab;e looks like after the MERGE), then I could show you exactly. how to do this.

  • mathguy
    mathguy Member Posts: 10,572 Blue Diamond

    Here is a minimal example for you to study. Notice how you can delete from the target when no matching row in the source: FIRST, the "source" as defined in MERGE must be the full outer join of the "source" and "target" tables in your real-life problem; SECOND, you use the WHEN MATCHED branch, which is the only one that has a DELETE clause; you delete the rows (from the "target" table) where there is no corresponding row in the source (in this example, those rows are recognized by a NULL source id in the outer join).

    Below I set up the example, then I show the MERGE statement, and then I show the resulting "target" table. Notice the two rows in table T that didn't correspond to anything in S: They are gone after the MERGE operation. The remaining row from T was updated, while the extra row from S was inserted. All three DML operations are demonstrated simultaneously; this includes the one you inquired about.

    drop table t purge;
    drop table s purge;
    
    create table t (id number primary key, str varchar2(10));
    create table s (id number primary key, str varchar2(10));
    
    insert into t
      select 101, 'abc' from dual union all
      select 102, 'def' from dual union all
      select 108, 'xqx' from dual
    ;
    
    insert into s
      select 102, 'dad' from dual union all
      select 104, 'mum' from dual
    ;
    
    commit;
    
    
    
    merge into t tgt
      using ( select t.id as t_id, s.id as s_id, s.str as s_str
              from   t full outer join s on t.id = s.id
            ) src       --  The source is this join, not the table S alone
            on (tgt.id = src.t_id)
    when matched then
      update
        set str = src.s_str
      delete            --  This is the DELETE you asked about
        where src.s_id is null
    when not matched then
      insert (id, str) values (src.s_id, src.s_str)
    ;
    
    
    
    select * from t;
    
       ID STR      
    ----- ----------
      102 dad      
      104 mum 
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,580 Red Diamond
    edited Jan 28, 2021 9:46PM

    Unlike SQL Server Oracle doesn't have WHEN NOT MATCHED BY SOURCE/BY TARGET, just WHEN NOT MATCHED so you'd have to build source in such a way that it has something like a flag, e.g. 'INSERT', 'UPDATE','DELETE'. For example:

    DROP TABLE TARGET PURGE
    /
    DROP TABLE SOURCE PURGE
    /
    CREATE TABLE TARGET
      AS
        SELECT  EMPNO,
                ENAME,
                DEPTNO,
                SAL
          FROM  EMP
          WHERE DEPTNO != 10
    /
    CREATE TABLE SOURCE
      AS
        SELECT  EMPNO,
                ENAME,
                DEPTNO,
                SAL + 1000 SAL
          FROM  EMP
          WHERE DEPTNO != 30
    /
    SQL> SELECT  *
      2    FROM  TARGET
      3  /
    
         EMPNO ENAME          DEPTNO        SAL
    ---------- ---------- ---------- ----------
          7369 SMITH              20        800
          7499 ALLEN              30       1600
          7521 WARD               30       1250
          7566 JONES              20       2975
          7654 MARTIN             30       1250
          7698 BLAKE              30       2850
          7788 SCOTT              20       3000
          7844 TURNER             30       1500
          7876 ADAMS              20       1100
          7900 JAMES              30        950
          7902 FORD               20       3000
    
    11 rows selected.
    
    SQL> SELECT  *
      2    FROM  SOURCE
      3  /
    
         EMPNO ENAME          DEPTNO        SAL
    ---------- ---------- ---------- ----------
          7369 SMITH              20       1800
          7566 JONES              20       3975
          7782 CLARK              10       3450
          7788 SCOTT              20       4000
          7839 KING               10       6000
          7876 ADAMS              20       2100
          7902 FORD               20       4000
          7934 MILLER             10       2300
    
    8 rows selected.
    
    SQL>
    

    Now we want to merge and update rows in target with info from a matching row in source, insert source rows not present in target and delete target rows not present in source:

    MERGE
      INTO TARGET T
      USING (
             SELECT  T.ROWID T_RID,
                     S.*,
                     CASE
                       WHEN S.ROWID IS NULL THEN 'DELETE'
                       WHEN T.ROWID IS NULL THEN 'INSERT'
                       ELSE 'UPDATE'
                     END FLAG
               FROM      TARGET T
                     FULL JOIN
                         SOURCE S
                       ON T.EMPNO = S.EMPNO
            ) S
        ON (
                T.ROWID = S.T_RID
            AND
                S.FLAG != 'INSERT'
           )
        WHEN MATCHED
          THEN
            UPDATE
               SET T.DEPTNO = S.DEPTNO,
                   T.ENAME = S.ENAME,
                   T.SAL = S.SAL
               DELETE
                 WHERE S.FLAG = 'DELETE'
        WHEN NOT MATCHED
          THEN
            INSERT
            VALUES(
                   S.EMPNO,
                   S.ENAME,
                   S.DEPTNO,
                   S.SAL
                  )
    /
    
    
    14 rows merged.
    
    
    SQL> SELECT  *
      2    FROM  TARGET
      3  /
    
    
         EMPNO ENAME          DEPTNO        SAL
    ---------- ---------- ---------- ----------
          7369 SMITH              20       1800
          7566 JONES              20       3975
          7788 SCOTT              20       4000
          7876 ADAMS              20       2100
          7902 FORD               20       4000
          7782 CLARK              10       3450
          7934 MILLER             10       2300
          7839 KING               10       6000
    
    8 rows selected.
    
    SQL>
    
    

    As you can see department 30 which isn't in source is gone from target, department 10 which ins't in target was inserted and department 20 which is in both is updated.

    SY.

  • mathguy
    mathguy Member Posts: 10,572 Blue Diamond
    edited Jan 28, 2021 9:59PM

    You are doing two weird things in your MERGE statement (which is otherwise identical to the example I gave).

    First, in the ON clause of MERGE, you have the condition S_FLAG != 'INSERT'. That condition is superfluous: the first ON condition is T.ROWID = S.T_RID, which means in particular that T.ROWID can't be NULL. But S_FLAG by definition is 'INSERT' exactly when T.ROWID is NULL, so the S_FLAG condition is already implied by the other condition.

    Which, then, means that you didn't need to bother to define S_FLAG = 'INSERT' to begin with. But, the second weird thing: you didn't need to define any flag. Instead of a condition like S.FLAG = 'DELETE', you could simply use the condition that defined that flag in the first place (in this case: S.ROWID IS NULL). Perhaps the concern is that the "source view" dind't include S.ROWID; that can be fixed directly, rather than by creating a flag column.

  • mathguy
    mathguy Member Posts: 10,572 Blue Diamond

    @Solomon Yakobson

    On further thought, perhaps the "second thing" isn't all that weird; why carry the full S.ROWID when you can carry far less information into the MERGE. (A similar idea would work in my example, where I had primary keys in the tables.)

    That might be implemented a little better like this:

    ... , case when S.ROWID is null then 1 end as DELETE_FLAG ...

    and then delete where DELETE_FLAG = 1

  • mathguy
    mathguy Member Posts: 10,572 Blue Diamond

    Sorry, I don't read any code unless I understand, before reading the code, what it is supposed to do. Your explanation in words (before jumping to code) is unclear. Please give us more details in plain English, don't ask us to figure them out from the code (which, presumably, does NOT do what it needs to do - otherwise you wouldn't be asking questions about it).

    You say you receive the "source" data one row at a time. What does that mean? Do you need to "merge" after each one-row communication? That is insane; unless you described your task incorrectly in the original post, this would mean that after each MERGE, the "target" table will have exactly one row, because you must delete from the target all the rows that don't exist in your (one-row) source. It is very unlikely that that's what you need to do.

    Otherwise, what is happening with the source rows that arrive "one at a time"? Are you staging them somewhere? That's what would make the most sense - and then use the "staging" table for a MERGE. But if that is the case, then why does it matter HOW the staging table (the source for MERGE) was populated, whether it was one row at a time, or all rows at once, or in any other manner?

    (As an aside, you refer to HUB and SPOKE as if everyone is supposed to know what that means. I don't. Does that even matter for understanding the problem? I can't tell.)

    I hope you understand why I am confused. Your turn! And, again, any attempt like "please read the code, you will understand" will be ignored.