MERGE Statement
Translation reference to convert Oracle MERGE statement to Snowflake Scripting
Description
The
MERGE
statement is used to select rows from one or more sources for update or insertion into a table or view. It is possible to specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets to avoid multipleINSERT
,UPDATE
, andDELETE
DML statements.MERGE
is a deterministic statement. It is not possible to update the same row of the target table multiple times in the sameMERGE
statement. (Oracle PL/SQL Language Reference MERGE Statement))
MERGE [ hint ]
INTO [ schema. ] { table | view } [ t_alias ]
USING { [ schema. ] { table | view }
| ( subquery )
} [ t_alias ]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ]
[ error_logging_clause ] ;
merge_update_clause := WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
[, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
merge_insert_clause := WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
[, { expr | DEFAULT } ]...
)
[ where_clause ]
error_logging_clause := LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED } ]
where_clause := WHERE condition
MERGE INTO <target_table> USING <source> ON <join_expr>
{ matchedClause | notMatchedClause } [ ... ]
matchedClause ::= WHEN MATCHED [ AND <case_predicate> ]
THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]
notMatchedClause ::= WHEN NOT MATCHED [ AND <case_predicate> ]
THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )
Sample Source Patterns
Sample auxiliary data
CREATE TABLE people_source (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
title VARCHAR2(10) NOT NULL
);
CREATE TABLE people_target (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
title VARCHAR2(10) NOT NULL
);
CREATE TABLE bonuses (
employee_id NUMBER,
bonus NUMBER DEFAULT 100
);
INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');
INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM hr.employees e, oe.orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);
MERGE Statement simple case
Oracle
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM people_target;
Snowflake
MERGE INTO PUBLIC.people_target pt USING PUBLIC.people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM PUBLIC.people_target ORDER BY person_id;
MERGE Statement with DELETE and where clause
In order to find an equivalence for the DELETE statement and the where clause, it is necessary to reorder and implement some changes in the Snowflake merge statement.
Changed required:
Replace the Oracle's DELETE where_clause with a new Snowflake's matchedClause with the AND predicate statement
Replace the where_clause from the Oracle's merge_insert_clause with an AND predicate statement in the Snowflake's notMatchedClause
Oracle
MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM hr.employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);
SELECT * FROM bonuses ORDER BY employee_id;
Snowflake
/*** MSC-WARNING - MSCEWI3114 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE ***/
MERGE INTO PUBLIC.bonuses D USING (SELECT employee_id, salary, department_id
FROM hr.employees
WHERE department_id = 80) S ON (D.employee_id = S.employee_id)
WHEN MATCHED AND (S.salary > 8000) THEN DELETE
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED AND (S.salary <= 8000) THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01);
SELECT * FROM bonuses ORDER BY employee_id;
In some cases the changes applied may do not work as expected, like the next example:
Oracle
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
DELETE where pt.title = 'Mrs.'
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title)
WHERE ps.title = 'Mr';
SELECT * FROM people_target;
Snowflake
/*** MSC-WARNING - MSCEWI3114 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE ***/
MERGE INTO PUBLIC.people_target pt USING PUBLIC.people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED AND pt.title = 'Mrs.' THEN DELETE
WHEN MATCHED THEN UPDATE SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED AND ps.title = 'Mr' THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM people_target ORDER BY person_id;
Known Issues
1. Oracle's error_logging_clause is not supported
There is no equivalent for the error logging clause in Snowflake Scripting.
2. Changed applied do not work as expected
Sometimes the changes applied in order to achieve the functional equivalence between Oracle's merge statement and Snowflake's, do not work as expected.
Related EWIs
MSCEWI3113: Merge statement error logging clause is not supported by Snowflake Scripting
MSCEWI3114: The Snowflake merge statement may have some functional differences compared to Oracle.
Last updated
Was this helpful?