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 multiple INSERT, UPDATE, and DELETE DML statements. MERGE is a deterministic statement. It is not possible to update the same row of the target table multiple times in the same MERGE statement. (Oracle PL/SQL Language Reference MERGE Statement))

Oracle MERGE Syntax

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

Snowflake Scripting MERGE Syntax

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

This code was executed for a better understanding of the examples:

Oracle

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);

Snowflake

MERGE Statement simple case

Oracle

Query

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;

Result

Snowflake

Query

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;

Result

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

Query

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;

Result

Snowflake

Query

/*** 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;

Result

In some cases the changes applied may do not work as expected, like the next example:

Oracle

Query

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;

Result

Snowflake

Query

/*** 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;

Result

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

1. MSCEWI3113: Merge statement error logging clause is not supported by Snowflake Scripting

2. MSCEWI3114: The Snowflake merge statement may have some functional differences compared to Oracle.