MSCEWI3114

Merge statement may do not work as expected

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is used to indicate that the Snowflake merge statement may have some functional differences compared to Oracle.

Example Code

Input Code:

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

Output Code:

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

Recommendations

• If you are getting different results compared to Oracle, consider the following:

  • For execution order prioritization, go to the next link to get more information.

    • Execute the skipped DML statements outside (before or after accordingly) the merge statement.

• If you need more support, you can email us at [email protected]