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

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

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

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE 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
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE 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
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE bonuses (
    employee_id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    bonus NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ DEFAULT 100
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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

Query

IN -> Oracle_02.sql

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

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

Snowflake

Query

OUT -> Oracle_02.sql

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

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

Query

IN -> Oracle_03.sql

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

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

Query

OUT -> Oracle_03.sql

--** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO 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 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

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

Query

IN -> Oracle_04.sql

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

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

Snowflake

Query

OUT -> Oracle_04.sql

--** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
MERGE INTO people_target pt USING 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;

Result

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.

Related EWIs

1. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
2. SSC-FDM-OR0018: Merge statement may not work as expected