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

[Prashant777]

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

ON

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()

)

[-werners-]

you have duplicates in your incoming data according to the join condition (Key_Id in this case).

The way to handle this is to get rid of the dups before you do the merge.

[Prashant777]

Thankuu werners for your answer.. is i have to remove duplicate in same code?...can you provide me code?

[-werners-]

first you have to find out what the cause of the duplicates is.

it might be that you try to join on an incomplete key. In that case you have to change your join condition.

Or perhaps you can just do a dropduplicates/distinct.

I never use sql btw to prepare data, imo you lose a lot of flexibility.

[Tread]

Hey as previously stated you could drop the duplicates of the columns that contain the said duplicates(code you can find online pretty easily), I have had this problem myself and it came when creating a temporary view from a dataframe, the dataframe didnt include duplicates but the tempview did hope this helps