Good morning.
1. There is a source table with fields A, B. Primary key is Column A. It has few records.
2. There is a target table with the same fields and data types as the source table. It also has few records.
3. Requirement is to merge the data from Source table to Target table as follows:
3a. If the target table has a record for given source record (based on the primary key), then update the remaining fields of that target record.
3b. If the target table does not have a record for a given source record (based on the primary key), then insert the record into target table.
3c. Delete the records from the Target table for which there is no matching record (based on the primary key) in the Source table.
DELETE statement in the MATCHED condition is working only when a specific condition in the UPDATE statement is commented out. It is supposed to work even if that condition is uncommented out.
Please try two runs. Once commenting it out, and the other uncommenting it.
I would like to understand what am I missing?
-- DROP TABLE target_tab;
-- DROP TABLE source_tab;
CREATE TABLE source_tab(a NUMBER PRIMARY KEY, b NUMBER);
INSERT INTO source_tab(a, b) VALUES (1, 1);
INSERT INTO source_tab(a, b) VALUES (2, 2);
INSERT INTO source_tab(a, b) VALUES (3, 3);
COMMIT;
CREATE TABLE target_tab(a NUMBER PRIMARY KEY, b NUMBER);
INSERT INTO target_tab(a, b) VALUES (1, 2);
INSERT INTO target_tab(a, b) VALUES (2, 2);
INSERT INTO target_tab(a, b) VALUES (4, 4);
INSERT INTO target_tab(a, b) VALUES (5, 5);
COMMIT;
-- Merge: DELETE statement in the MATCHED condition is working only when the specific line in the UPDATE statement is commented out begin.
MERGE INTO target_tab tgt
USING (SELECT NVL(s.a, t.a) a_whichever_is_not_null,
s.a s_a, s.b s_b,
t.a t_a, t.b t_b
FROM source_tab s FULL JOIN target_tab t ON (s.a = t.a)) fojv
ON (fojv.a_whichever_is_not_null = tgt.a)
WHEN MATCHED THEN
UPDATE SET tgt.b = fojv.s_b
WHERE fojv.t_a IS NOT NULL
-- AND fojv.s_a IS NOT NULL
DELETE
WHERE fojv.s_a IS NULL AND fojv.t_a IS NOT NULL
WHEN NOT MATCHED THEN
INSERT (tgt.a, tgt.b)
VALUES (fojv.s_a, fojv.s_b);
-- Merge: DELETE statement in the MATCHED condition is working only when the specific line in the UPDATE statement is commented out end.
From the docs:
The only rows affected by this clause are those rows in the destination table that are updated by the merge operationSo the merge statement has to change a row to be able to remove it. If the WHEN MATCHED clause has both
UPDATE ... fojv.s_a IS NOT NULL
and
DELETE ... fojv.s_a IS NULL
The rows where fojv.s_a is null aren't updated, so can't be deleted.