Merge with MATCHED BY TARGED/MATCHED BY SOURCE syntax

Martin Preiss

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.

Lothar Flatz

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

Sven W.

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

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

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