[Oracle] Beispiel für gleichzeitiges Insert/ Update/ Delete mit einem Merge-Statement

[Thomas Darimont]

Hallo,

hier ein kleines Beispiel wie man unter Oracle mit dem Merge-Statement gleichzeitig:
- neue Datensätze einfügt
- alte Datensätze mit neuen Daten updated (Änderungen)
- und alte Datensätze ohne neue Daten löscht

Diese Art von Merge ist insbesondere dann hilfreich, wenn die Zieltabelle einen nicht historisierten aktuellen Zustand abbilden soll.

Hier die Dokumentation zum Merge SQL-Statement in Oracle:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Hier ein Beispiel für einen klassischen Anwendungsfall für Merge:
http://psoug.org/reference/merge.html

Setup:

DROP TABLE t_target;
CREATE TABLE t_target (
fk INT,    --Business Key
val VARCHAR2(32),
load_id INT --Nr der Datenladung 
);
 
--Initialer Datenbestand
INSERT INTO t_target (fk, val,load_id) VALUES (1,'X',1);
INSERT INTO t_target (fk, val,load_id) VALUES (3,'Z',1);

Inhalt der Tabelle test_info vor dem Merge:

SQL> SELECT * FROM t_target;;
                                    FK VAL                                                             LOAD_ID
-------------------------------------- -------------------------------- --------------------------------------
                                     1 X                                                                     1 
                                     3 Z                                                                     1 

SQL>

Unser Merge:

MERGE INTO t_target t --merge target
USING (
WITH t_source AS ( 
/* Szenario: 
 * 1) der Wert val von dem Datensatz mit dem FK 1 soll nach dem Merge den Wert AA haben -> update
 * 2) Der Datensatz mit FK 2 wird neu eingefügt --> insert
 * 3) Der Datensatz mit FK 3 der nicht mehr in den neuen Daten enthalten ist soll in der Ziel Tabelle gelöscht werden -> delete
 */
    SELECT 1 AS fk, 'A' AS val, 2 AS load_id FROM dual
    UNION
    SELECT 2 AS fk, 'B' AS val, 2 AS load_id FROM dual
    UNION
    SELECT 1 AS fk, 'AA' AS val, 3 AS load_id FROM dual
)
, merge_input AS(
    SELECT t.*,'old' AS art FROM t_target t -- alte daten
    UNION ALL
    SELECT s.*,'new' AS art FROM t_source s  -- neue daten
)
, ranked_merge_input AS(
SELECT 
    i.*
  , ROW_NUMBER() OVER(PARTITION BY fk ORDER BY load_id DESC) rn_latest_record 
FROM 
  merge_input i
)
SELECT 
  * 
FROM 
  ranked_merge_input 
WHERE rn_latest_record = 1 -- nur die (letzten) effektiven Änderungen berücksichtigen
) s  --merge source
ON (t.fk = s.fk)
WHEN MATCHED THEN     UPDATE SET 
                                  val = s.val
                                , load_id = CASE WHEN s.art = 'old' THEN NULL ELSE s.load_id END --Nicht mehr benötigte 
                                                                                                 --alte Sätze als zu löschen markieren 
                             WHERE t.fk = s.fk
                      DELETE 
                             WHERE t.load_id IS NULL
WHEN NOT MATCHED THEN INSERT (fk,val,load_id) 
                             VALUES (s.fk, s.val,s.load_id)
;

Inhalt der Tabelle test_info nach dem Merge:

SQL> SELECT * FROM t_target;
                                    FK VAL                                                             LOAD_ID
-------------------------------------- -------------------------------- --------------------------------------
                                     1 AA                                                                    3 
                                     2 B                                                                     2 

SQL>

Gruß Tom