Databricks support updating multiple target rows with single matching source row in merge query?

[Shruti12]

Hi,

I am getting this error in merge statement. DeltaUnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same target row in the Delta table in possibly conflicting ways.

Does Databricks support updating multiple records with single row coming from source in a merge query (we are getting multiple target rows based on match condition for each single source row)?  Basically, source- 1 row, Target- many rows

[szymon_dybczak]

Hi @Shruti12 ,

Yes it does. Check below example, in this case one row from source_table caused an updated of 2 matching rows in target_table.

%sql
CREATE OR REPLACE TABLE default.target_table (
  id INT,
  category STRING,
  value STRING
) USING DELTA;

INSERT INTO target_table VALUES
(1, 'A', 'old'),
(2, 'A', 'old'),
(3, 'B', 'old');


%sql
CREATE OR REPLACE TABLE default.source_table (
  category STRING,
  new_value STRING
) USING DELTA;

INSERT INTO source_table VALUES
('A', 'new');


%sql
MERGE INTO target_table AS target
USING source_table AS source
ON target.category = source.category
WHEN MATCHED THEN
  UPDATE SET value = source.new_value;

But your error suggests different scenario. It seems that in your data you have multiple source rows that match one target row. In that case you have ambiguity and merge will not work.

In DBR 16.1 + they improve 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. You can read about this under following post:

Improved Duplicate Match Handling in Delta MERGE O... - Databricks Community - 106537

[Shruti12]

Hi @szymon_dybczak ,

Thanks for your reply. The above code is working fine which means multiple updates can be done from a single source target. So, it may be when there are complex matching conditions/values, merge query gives error.

I cannot send you exact code. However, I tried to mimic the query in case you have any further suggestions. For below query, I am getting merge conflict error as mentioned above. 

Here, I am trying to update the target table based on a source table. However, since there were multiple records in source table, I used max and group by to get unique record. I checked using count, we are having distinct record in source table query,S1 after applying max and group by. However, the query is still giving me merge conflict error saying multiple sources. I further researched to find out that sometimes when we have multiple records in target table, then also we get this error. But could not find anything concrete on Databricks website on this. Please suggest.

%sql

merge into target table T

using(select max(col1) as col1,max(col2) as col2 from source table S where col3='A' group by col 4,col 5,col 6)s1)

on upper(trim(T.col4)) = upper(trim(s1.col4)) and upper(T.col5) = upper(S1.col5)

and upper(T.col6) =upper(S1.col6) and T.col3='A'

when matched then update set

T.col1=s1.col1, T.col2=s1.col2

Please Note: The above code is working fine for few cases and giving error for other cases.