Can this SQL MERGE table copy be written as a single operation?

Question

Using the DB2 for i platform, I have been successfully using two operations to do a whole-table data update from one table to another:

-- put values from source file into target file
MERGE INTO TLIB.TABLE AS T USING SLIB.TABLE AS S
  ON (T.KEY1 = S.KEY1 AND T.KEY2  = S.KEY2)
WHEN NOT MATCHED THEN
  INSERT VALUES(S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED THEN
  UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1);

-- remove records that are in target file but not source file
DELETE FROM TLIB.TABLE T2
WHERE RRN(T2) IN (
  SELECT RRN(T)
  FROM TLIB.TABLE AS T
  LEFT JOIN SLIB.TABLE AS S
    ON (T.KEY1 = S.KEY1 AND T.KEY2 = S.KEY2)
  WHERE S.KEY1 IS NULL);

This has been giving me the result I want, but I can't help think that there is a way to make this into one statement, maybe some kind of join on the source table inside of the merge statement.

Answer 1

If you do a full join of the source and target tables before you merge, you can then tell which records exist in one, the other, or both and adjust your merge actions accordingly:

MERGE INTO TLIB.TABLE AS T USING (
  SELECT S1.*, T1.KEY1 AS TKEY1, T1.KEY2 AS TKEY2
  FROM SLIB.TABLE AS S1
  FULL OUTER JOIN TLIB.TABLE AS T1
    ON T1.KEY1 = S1.KEY1 AND T1.KEY2 = S1.KEY2
  ) AS S ON COALESCE(S.KEY1, TKEY1) = T.KEY1 AND
            COALESCE(S.KEY2, TKEY2) = T.KEY1
WHEN NOT MATCHED THEN
  INSERT VALUES(S.KEY1, S.KEY2, S.FIELD1, S.FIELD1)
WHEN MATCHED AND S.KEY1 IS NULL THEN DELETE
WHEN MATCHED AND S.KEY1 IS NOT NULL THEN 
  UPDATE SET ROW = (S.KEY1, S.KEY2, S.FIELD1, S.FIELD1);