Snowflake Documentation Home

MERGE Statement

Translation reference to convert Oracle MERGE statement to Snowflake Scripting

Some parts in the output code are omitted for clarity reasons.

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:

IN -> Oracle_01.sql
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
    );