A Boolean property that determines whether batch DELETE statements are automatically converted to MERGE statements. This applies only when the DELETE statement’s WHERE clause exclusively contains the table’s primary key fields combined using the AND logical operator. When enabled, Snowflake attempts to handle deletions more efficiently by using the MERGE mechanism instead of standard batch deletion.

Data Type

bool

Default Value

false

Remarks

MergeDelete allows for upsert functionality. This property is primarily intended for use with tools where you have no direct control over the queries being executed. Otherwise, as long as Query Passthrough is 'true', you could execute the MERGE command directly.

When this property is 'false', DELETE bulk statements are executed against the server. When it is set to 'true' and the DELETE query contains the primary key field, Snowflake sends a MERGE query that executes a DELETE if match is found in Snowflake.

For example this query:

DELETE FROM "Table" WHERE "ID" = 1 AND "NAME" = 'Jerry'

is sent to Snowflake as the following MERGE request:

MERGE INTO "Table" AS "Target" USING "RTABLE1_TMP_20eca05b-c050-47dd-89bc-81c7f617f877" AS "Source" ON ("Target"."ID" = "Source"."ID" AND "Target"."NAME" = "Source"."NAME") 
WHEN MATCHED THEN DELETE