topic Re: UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same in Data Engineering

UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same

Prashant777 — Mon, 15 May 2023 07:07:37 GMT

My Code:--

CREATE OR REPLACE TEMPORARY VIEW preprocessed_source AS

SELECT

Key_ID,

Distributor_ID,

Customer_ID,

Customer_Name,

Channel

FROM integr_masterdata.Customer_Master;

-- Step 2: Perform the merge operation using the preprocessed source table

MERGE INTO slvr_masterdata.Customer_Master as Target

USING preprocessed_source AS Source

Source.Key_ID = Target.Key_ID

WHEN MATCHED THEN

UPDATE SET

Target.Distributor_ID = Source.Distributor_ID,

Target.Customer_ID = Source.Customer_ID,

Target.Customer_Name = Source.Customer_Name,

Target.Channel = Source.Channel,

Target.Time_Stamp = current_timestamp()

WHEN NOT MATCHED

THEN INSERT

(

Distributor_ID,

Customer_ID,

Customer_Name,

Channel,

Time_Stamp

)

VALUES (

Source.Distributor_ID,

Source.Customer_ID,

Source.Customer_Name,

Source.Channel,

current_timestamp()

)

Re: UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same

daniel_sahal — Tue, 16 May 2023 05:47:04 GMT

@Prashant Joshi

Since you're doing merge based on Source.Key_ID = Target.Key_ID, you need to ensure that Key_ID is unique in your source table. Otherwise it will throw an error as it cannot determine which row it should update.

Re: UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same

Anonymous — Tue, 23 May 2023 09:20:16 GMT

Hi @Prashant Joshi

Hope everything is going great.

Just wanted to check in if you were able to resolve your issue. If yes, would you be happy to mark an answer as best so that other members can find the solution more quickly? If not, please tell us so we can help you.

Cheers!

Re: UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempte

LokeshManne — Wed, 14 May 2025 08:49:29 GMT

@Prashant777

In your scenario at update section, you are trying to update primary keys aswell, which Delta Table can't differentiate when you re-run the same batch/file and throws error as all duplicates, to run without error/fail, remove (Target.Distributor_ID = Source.Distributor_ID, Target.Customer_ID = Source.Customer_ID) columns from UPDATE clause. Updated code below.

MERGE INTO slvr_masterdata.Customer_Master as Target

USING preprocessed_source AS Source

ON Source.Key_ID = Target.Key_ID

WHEN MATCHED THEN

UPDATE SET

Target.Customer_Name = Source.Customer_Name,

Target.Channel = Source.Channel,

Target.Time_Stamp = current_timestamp()

WHEN NOT MATCHED

THEN INSERT

(

Distributor_ID,

Customer_ID,

Customer_Name,

Channel,

Time_Stamp

)

VALUES (

Source.Distributor_ID,

Source.Customer_ID,

Source.Customer_Name,

Source.Channel,

current_timestamp()

)