MERGE Examples
1. Add transaction incremental balances to a master account table. If an account is not in the master table yet, it is added.
CREATE TABLE master_table (
acct_no INTEGER NOT NULL,
balance FLOAT4);
INSERT INTO master_table VALUES (1,23.9),(3,18.5);
CREATE TABLE trx (
acct_no INTEGER NOT NULL,
balance FLOAT4);
INSERT INTO trx values (2,23.9),(3,29.8);
MERGE INTO master_table t USING trx x ON t.acct_no = x.acct_no
WHEN MATCHED THEN UPDATE SET balance = t.balance + x.balance
WHEN NOT MATCHED THEN INSERT VALUES (x.acct_no, x.balance);
2. In the previous example, if there are multiple transaction table rows for a new account, multiple master rows get inserted for the same acct_no. This happens because all MATCHED / NOT MATCHED decisions are made before any updates, inserts, or deletes occur.
If there may be multiple new-account transaction rows, a better way to do this merge is:
DELETE FROM trx;
INSERT INTO trx values (5,33.9),(4,2.63), (1,99.9);
MERGE INTO master_table t
USING (SELECT acct_no, sum(balance) AS balance FROM trx GROUP BY acct_no) x ON t.acct_no = x.acct_no
WHEN MATCHED THEN UPDATE SET balance = t.balance + x.balance
WHEN NOT MATCHED THEN INSERT VALUES (x.acct_no, x.balance);
This MERGE processes each transaction acct_no exactly once.
3. This example is the same as the previous one, except that we now assume that a NULL balance in the transaction table means that that account is to be deleted (or never recorded, if it is a new account).
DELETE FROM trx;
INSERT INTO trx values (5,33.9),(4,2.63), (3,NULL);
MERGE INTO master_table t
USING (SELECT acct_no,
MAX(CASE WHEN balance IS NULL THEN 1 ELSE 0 END) as delete_flag,
SUM(balance) AS balance
FROM trx GROUP BY acct_no) x
ON t.acct_no = x.acct_no
WHEN MATCHED AND delete_flag = 1 THEN DELETE
WHEN MATCHED THEN UPDATE SET balance = t.balance + x.balance
WHEN NOT MATCHED AND delete_flag <>1 THEN INSERT VALUES (x.acct_no, x.balance);
The second WHEN MATCHED clause could have been written with a condition "AND delete_flag <> 1" without changing the semantics of the MERGE statement. (The statement is easier to understand without the unnecessary condition.)
4. Example:
MERGE INTO t USING s ON (t.i = s.i)
-- branch 1
WHEN MATCHED AND (s.op = 1) THEN UPDATE SET t.j = t.j + s.j
-- branch 2
WHEN NOT MATCHED AND (s.op = 2) THEN INSERT VALUES (s.i, 2 * s.j)
-- branch 3
WHEN MATCHED AND (s.op = 3) THEN DELETE
-- branch 4
WHEN MATCHED THEN UPDATE SET t.j = t.j - 4 * s.j
-- branch 5
WHEN NOT MATCHED THEN INSERT VALUES (s.i, 5 * s.j)
Last modified date: 08/29/2024