Azure Synapse SQL Merge is not updating records, instead of that it inserts matching records using spark.sql

Question

I have the below code where the Id is a 36 character GUID. The code gets executed but when a matching record is found , instead of updating it inserts the entire records again. What could be the root cause for this?

delta_table.alias("target").merge( deduped_df.alias("source"), "trim(upper(target.Id)) = trim(upper(source.dId)) " ).whenMatchedUpdate( set={ "Id" : "source.dId", "EntityId" : "source.EntityId", "PropertyName" : "source.PropertyName", "ValueString":"source.ValueString", "ValueInt" : "source.ValueInt", "ValueDecimal" : "source.ValueDecimal", "ValueBit" : "source.ValueBit", "ValidFrom" : "source.ValidFrom", "ValidTo" : "source.ValidTo", "Description" : "source.Description", "ModifiedBy" : "source.ModifiedBy", "CreatedAt" : "source.CreatedAt", "CreatedBy" : "source.CreatedBy", "Active" : "source.Active", "Saved" : "source.Saved", "ETL_UpdateDate" : "source.ETL_UpdateDate", "ETL_Source" : "source.ETL_Source" }).whenNotMatchedInsert(values={ "Id" : "source.dId", "EntityId" : "source.EntityId", "PropertyName" : "source.PropertyName", "ValueString":"source.ValueString", "ValueInt" : "source.ValueInt", "ValueDecimal" : "source.ValueDecimal", "ValueBit" : "source.ValueBit", "ValidFrom" : "source.ValidFrom", "ValidTo" : "source.ValidTo", "Description" : "source.Description", "ModifiedBy" : "source.ModifiedBy", "CreatedAt" : "source.CreatedAt", "CreatedBy" : "source.CreatedBy", "Active" : "source.Active", "Saved" : "source.Saved", "ETL_UpdateDate" : "source.ETL_UpdateDate", "ETL_LoadDate" : "source.ETL_LoadDate", "ETL_Source" : "source.ETL_Source" }).execute()

has it been solved ? anything from my answer was useful ? if not you can add comment — Ram Ghadiyaram
– Ram Ghadiyaram, Commented Nov 8 at 16:09

Ram Ghadiyaram · Accepted Answer · 2025-08-02 06:05:53Z

0

I think Your merge condition is not matching eventhough your ids on both side of the records are matching

check the data.

trim(upper(target.Id)) = trim(upper(source.dId))

explicitly cast to string type before join

"trim(upper(cast(target.Id as string))) = trim(upper(cast(source.dId as string)))`

check uniqueness on each side like your df.groupBy("dId").count().filter("count > 1").show()

NOTE : 36 character GUID may be equal but may have different representations like wrapped with curly brace and plain text make sure that they are uniform

answered Aug 2 at 6:05

Ram Ghadiyaram

29.4k16 gold badges101 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure Synapse SQL Merge is not updating records, instead of that it inserts matching records using spark.sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related