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

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

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

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

CREATE OR REPLACE TABLE PUBLIC.people_source (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
title VARCHAR(10) NOT NULL);

CREATE OR REPLACE TABLE PUBLIC.people_target (
person_id  INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
title VARCHAR(10) NOT NULL);

CREATE OR REPLACE TABLE PUBLIC.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;

PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        3|Jane      |Doe      |Miss |
        4|Dave      |Brown    |Mr   |

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;

PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        3|Jane      |Doe      |Miss |
        4|Dave      |Brown    |Mr   |

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;

EMPLOYEE_ID|BONUS|
-----------+-----+
        153|  180|
        154|  175|
        155|  170|
        159|  180|
        160|  175|
        161|  170|
        164|   72|
        165|   68|
        166|   64|
        167|   62|
        171|   74|
        172|   73|
        173|   61|
        179|   62|

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;

EMPLOYEE_ID|BONUS|
-----------+-----+
        153|  180|
        154|  175|
        155|  170|
        159|  180|
        160|  175|
        161|  170|
        164|   72|
        165|   68|
        166|   64|
        167|   62|
        171|   74|
        172|   73|
        173|   61|
        179|   62|

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;

PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        4|Dave      |Brown    |Mr   |

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;

PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
        1|John      |Smith    |Mr   |
        2|Alice     |Jones    |Mrs. |
        4|Dave      |Brown    |Mr   |

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.

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.

PreviousINSERT Statement Extension NextSELECT INTO Statement

Last updated 2 years ago

Description

Sample Source Patterns

Sample auxiliary data

MERGE Statement simple case

Oracle

Snowflake

MERGE Statement with DELETE and where clause

Changed required:

Oracle

Snowflake

Oracle

Snowflake

Known Issues

1. Oracle's error_logging_clause is not supported

2. Changed applied do not work as expected

Related EWIs