In DBR 16.1+, we’ve improved functionality of MERGE operations where multiple rows of the source dataset match the same row of the target Delta table, but only one row matches the WHEN MATCHED condition. In the past, these operations would fail with DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE.
Prior DBR’s MERGE would only use the MERGE conditions to detect multiple rows from the source dataset trying to update the target dataset leading to ambiguity. This improved functionality allows for the WHEN MATCHED condition to help resolve the ambiguity.
As an example, let's take a look at a MERGE that will now succeed in DBR 16.1+.
MERGE INTO t USING s
ON t.id = s.id
WHEN MATCHED AND s.value = 'b'
THEN UPDATE SET *
WHEN NOT MATCHED
THEN INSERT *
|
Target Table
Source Table
In this example, the source has two matching rows that could be used to update the target table. The WHEN MATCHED condition reduces the matching rows from two matches to one match. When running this with an older DBR, it would fail with DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE, even though the WHEN MATCHED condition reduced the matches to one. The improved functionality in DBR 16.1+ will allow this to succeed