Azure Synapse Merge Command is working with Error-"Updating a distribution key column in a MERGE statement is not supported."

Question

Hello,

I am doing a incremental load on target table using delta load from the source table based on certain conditions. It is supposed to insert/update/delete records from the source table to target table. I am achieving this through a sequence of processes in a stored proc. The store proc won't update the distribution key of any table involved throughout all the operations. I have validated that the MERGE command successfully updates/inserts/deletes record in the target table using source table, but when MERGE operation finishes, it throws the "Updating a distribution key column in a MERGE statement is not supported.". Operations post this MERGE command are skipped and system exits the Store Proc Execution

Azure Synapse Version:

Target Table DDL:

CREATE TABLE gffdw.POC_Chrtx_D
WITH
(
DISTRIBUTION = HASH(Chrtx_Sub_Account)
)
AS
SELECT * from gffdw.Chrtx_D

Source Table DDL

CREATE TABLE #trans2_filt_chrt_D
WITH
(
DISTRIBUTION = HASH(Chrtx_Sub_Account)
)
AS
SELECT * from #trans_filt_chrt_D

MERGE Query

Merge gffdw.POC_Chrtx_D POC
USING #trans2_filt_chrt_D inc
ON POC.chrtx_id = inc.chrt_id
WHEN MATCHED and inc.chrt_id<>'' and POC.tgt_hash_key<>inc.src_hash_key
THEN UPDATE /Records from inc to POC/
WHEN MATCHED and inc.chrt_id =''
THEN DELETE
WHEN NOT MATCHED and inc.chrt_id<>''
THEN INSERT /Records from inc to POC/;

Note: I have not listed out exact commands for insert/update as the tables have 100+ columns.

Post this MERGE operation in my SP, I have some additional DDL commands to be executed as follows

ALTER TABLE gffdw.STG_Filt_Chrt_D DROP COLUMN Chrt_Lang
ALTER TABLE gffdw.STG_Filt_Chrt_D DROP COLUMN Chrt_Cntry
ALTER TABLE gffdw.STG_Filt_Chrt_D DROP COLUMN Variant
ALTER TABLE gffdw.STG_Filt_Chrt_D Add Chrt_Key_Hash VARBINARY(8000)
ALTER TABLE gffdw.STG_Filt_Chrt_D Add Chrt_Row_Hash VARBINARY(8000)

All these ALTER commands are skipped due to the MERGE error.

Thanks
Bhanu

Answer 1

Hello @Bhanurdra Narayan Mohapatra ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is to get rid of the error "Updating a distribution key column in a MERGE statement is not supported", please do let us know if its not accurate.

So the reason you are getting the error is because the below statement

THEN UPDATE /Records from inc to POC/

is probably having a statement like

SET inc.Chrtx_Sub_Account = XXXXX

please beware that on the column Chrtx_Sub_Account a HASH distribution is set at the table level .

CREATE TABLE gffdw.POC_Chrtx_D
WITH
(
DISTRIBUTION = HASH(Chrtx_Sub_Account)
)

So removing the column from the update should help you .

I tried a simple query and pasting that below

CREATE TABLE dbo.himanshu_Distribution_hash_Source
(
id int NOT NULL,
description varchar(200)
)
WITH
(
DISTRIBUTION = HASH (id)
)

INSERT INTO himanshu_Distribution_hash_Source values (1,'This is an entry into himanshu_Distribution_hash_Source table')
select * from himanshu_Distribution_hash_Source

CREATE TABLE dbo.himanshu_Distribution_hash_Destination
(
id int NOT NULL,
description varchar(100)

)
WITH
(
DISTRIBUTION = HASH (id)

)

The below query will fail due to the same reason

MERGE himanshu_Distribution_hash_Destination as dest
using himanshu_Distribution_hash_Source as src
on src.id = dest.id
when matched then
Update Set
id = 1000
WHEN NOT MATCHED THEN
INSERT (id, description)
VALUES (src.id, src.description);

The below just works fine

MERGE himanshu_Distribution_hash_Destination as dest
using himanshu_Distribution_hash_Source as src
on src.id = dest.id
when matched then
** Update Set **
** description = 'Updated'**
WHEN NOT MATCHED THEN
INSERT (id, description)
VALUES (src.id, src.description);

This is called out here : https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=azure-sqldw-latest&preserve-view=true#remarks

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators