cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Prashant777
New Contributor II

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

)

4 REPLIES 4

-werners-
Esteemed Contributor III

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.

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

-werners-
Esteemed Contributor III

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
New Contributor II

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!