Merge, standard edition
MERGE is designed to use a small UPDATE table to affect a larger FACT table. This scenario primarily uses values from the UPDATE table and their primary key in the FACT table to
- insert new rows based on values from the UPDATE table
- update values in the FACT table according to values from both tables
- delete rows from the FACT table according to conditions based on both tables
MERGE INTO customers c
USING new_customers n
ON
(n.customer_no = c.customer_no)
WHEN MATCHED THEN
UPDATE SET
city_id=n.city_id
WHEN NOT MATCHED THEN
INSERT VALUES
(customer_no, first_name, last_name, gender, birthday, city_id);
Merge, reversed scenario
During ETL and quality assurance, MERGE can be used to apply constraint checks on a temporary table.
In this scenario, the large FACT table acts as source for modifications in the STAGING table:
UPDATE/DELETE rows that violate primary key or other logical constraints that need cross-referencing with data from the FACT table.
INSERT actions generally are not desired in this scenario, most cases can even be replaced with corresponding UPDATE TABLE statements using EXISTS or IN(SELECT) constructs. The advantage of MERGE here is the possibility to update the target table with the data from the source table:
MERGE INTO new_customers n
USING customer c
ON( c.customer_no=n.customer_no )
WHEN MATCHED THEN
UPDATE
SET error_flag=true,
error_text= 'Customer-id already exists with name ' || c.first_name || ',' ||c.last_name
WHERE
c.first_name||c.last_name != n.first_name||n.last_name
Merge as difference-finder
MERGE can also be used to detect differences between tables.
Assume you need to find all differences between today's and yesterday's excerpt of customers.
Normally, you would
- do a LEFT OUTER JOIN to find new (primary key) rows,
- do a RIGHT OUTER JOIN to find deleted (primary key) rows and
- do a INNER JOIN with filters to find modified (non primary key) rows.
All three steps can be done with a single MERGE command, but in this case it's destructive on one of the two tables:
- Add a 'status' column to today's table
- MERGE yesterday's table into today's table:
- INSERT all all non-matching rows (these have obviously been deleted since yesterday)
- UPDATE the status column of all matching rows (they are potentially modified), but DELETE all the rows that do not differ between the two tables (they are unmodified and therefore uninteresting)
All untouched rows (status-column is still default value) had no match in yesterday's data and are obviously new to the table.
The result you can now feed to 'Merge, the standard edition' and update your FACT table with that.
MERGE INTO new_customer n
USING customer c
ON
(c.customer_no=n.customer_no)
WHEN MATCHED THEN
UPDATE SET
status_flag='U'
DELETE WHERE
(c.first_name=n.first_name AND ... AND c.city_id=n.city_id)
WHEN NOT MATCHED THEN
INSERT VALUES
(customer_no, first_name, ..., city_id, 'D')
;
Result:
- Rows, that have been deleted since the previous day, did not exist in the table „today" anymore and were reinserted by applying the INSERT rule and bear the status_flag „D" for delete.
- Rows that still exist, receive the flag „U" for update by applying the UPDATE rule.
- Rows that did not change to the previous day are directly being deleted from the table „today" by applying the DELETE rule.
- Newly added rows that did not yet exist in „yesterday", remain unaffected by the merge and exclusively bear the default status flag (if applicable NULL).
Explanation