What is the behavior when merge key is not unique

[Brad]

Hi, 

When using the MERGE statement, if merge key is not unique on both source and target, it will throw error. If merge key is unique in source but not unique in target, WHEN MATCHED THEN DELETE/UPDATE should work or not? For example merge key is id. Src has 1 row id = 1, target has multiple rows with id = 1. WHEN MATCHED will update or delete all rows in target for id = 1 or pick up 1 randomly?

Thanks

Brad

[Brad]

Cool, this is what I tested out. Great to get confirmed. Thanks. BTW, https://medium.com/@ritik20023/delta-lake-upserting-without-primary-key-f4a931576b0 has a workaround which can fix the merge with duplicate merge key on both source and target.