Forum Stats

  • 3,824,847 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Merge with MATCHED BY TARGED/MATCHED BY SOURCE syntax

Martin Preiss
Martin Preiss Member Posts: 2,381 Gold Trophy
edited May 16, 2016 8:46AM in Database Ideas - Ideas

Again an idea for the category "product x has it already". This time product x is the SQL Server: there it has been possible to make a distinction between different kinds of mismatches in a merge since many releases: https://msdn.microsoft.com/de-de/library/bb510625(v=sql.120).aspx

"WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that does not match a row in target_table, but does satisfy an additional search condition, if present. [...]

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

Specifies that all rows of target_table that do not match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause."

This would make some data load operations in data warehouses more simple.

ctriebtonibony7Ajay Narvekard1b538fb-0530-4128-959f-7fbe89100233
6 votes

Active · Last Updated

Comments

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
    edited May 17, 2016 4:00AM

    Hi mArtin, Instead of makeing us think which ETL operations could possible benefit you should give an example. That would help me a lot.

    William Robertson
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited May 23, 2016 8:04AM

    I think that is already possible although the syntax is slightly different.

    The WHEN (NOT) MATCHED clauses can have additional where conditions, that would filter the inserted/updated/deleted rows.

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#BGBGIICE

    And here is a (German) post (I think from Carsten Czarski) that gives an example how to use it:

    http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1359936.html

  • tonibony7
    tonibony7 Member Posts: 14 Blue Ribbon

    I just published another idea - efficient update of both matched and unmatched rows in joined target, which is related to this idea. It describes a case where the extension "WHEN NOT MATCHED BY SOURCE" will be very useful.

  • d1b538fb-0530-4128-959f-7fbe89100233
    edited Jul 18, 2019 7:12PM

    Try this

    create table TESTE_SOURCE (COLUNAPK VARCHAR2(100) primary key, COLUNA2 VARCHAR2(100), FLG_REMOVE VARCHAR2(100));

    create table TESTE_TARGAT (COLUNAPK VARCHAR2(100) primary key, COLUNA2 VARCHAR2(100));

    INSERT ALL

    -- ORIGEM

      INTO TESTE_SOURCE (COLUNAPK, COLUNA2, FLG_REMOVE) VALUES ('1','-','N') -- UPDATE

      INTO TESTE_SOURCE (COLUNAPK, COLUNA2, FLG_REMOVE) VALUES ('2','2','N') -- KEEP

      INTO TESTE_SOURCE (COLUNAPK, COLUNA2, FLG_REMOVE) VALUES ('3','-','Y') -- DELETE

      INTO TESTE_SOURCE (COLUNAPK, COLUNA2, FLG_REMOVE) VALUES ('4','4','N') -- KEEP

      INTO TESTE_SOURCE (COLUNAPK, COLUNA2, FLG_REMOVE) VALUES ('5','5','N') -- NEW

    -- DESTINO

      INTO TESTE_TARGAT (COLUNAPK, COLUNA2) VALUES ('1','1')

      INTO TESTE_TARGAT (COLUNAPK, COLUNA2) VALUES ('2','2')

      INTO TESTE_TARGAT (COLUNAPK, COLUNA2) VALUES ('3','3')

      INTO TESTE_TARGAT (COLUNAPK, COLUNA2) VALUES ('4','4')

    SELECT * FROM dual;

    SELECT * FROM TESTE_TARGAT; -- PREVIUS STATE

    SELECT * FROM TESTE_SOURCE; -- NEW STATE

    /

    MERGE INTO TESTE_TARGAT t

       USING

       (

        SELECT s. COLUNAPK, s.COLUNA2, s. FLG_REMOVE

          FROM TESTE_SOURCE s

       ) s

       ON (t.COLUNAPK = s.COLUNAPK)

       WHEN MATCHED THEN

            UPDATE SET t.COLUNA2 = s.COLUNA2 -- UPDATE

            DELETE WHERE (s.FLG_REMOVE = 'Y') -- DELETE

       WHEN NOT MATCHED /*BY TARGET*/ THEN

            INSERT VALUES (s.COLUNAPK, s.COLUNA2) -- NEW

    ;

    /

    SELECT * FROM TESTE_TARGAT;  -- LATER STATE