Azure Synapse SQL Pool: Issue in MERGE INSERT UPDATE statement

Question

I have a table in Azure Synapse Dedicated SQL POOL.

CREATE TABLE table_A
  (
  ID int IDENTITY(1,1),
  ClientID    varchar(10),
  fitrstname  varchar(20),
  lastname    varchar(30),
  phone       varchar(20),
  address     varchar(100),
  milageRun   decimal(18,8)
  CONSTRAINT (PK_table_A) PRIMARY NONCLUSTERED
    (
      ClientID ASC
    )
   )
   WITH
    (
     DISTRIBUTION = ROUND_ROBIN,
     HEAP
    )
   GO

Now when I am trying to do a MERGE-INSERT-UPDATE the above table using the below syntax

  MERGE table_A AS TARGET
    USING table_B AS SOURCE
    ON table_A.ClientID = table_B.ClientID
  WHEN MATCHED THEN 
  UPDATE SET
  TARGET.milageRun = SOURCE.odometerCount
  IF NOT MATCHED BY TARGET THEN 
  INSERT INTO (milageRun)
  VALUES (SOURCE.odometerCount)

The above SQL script is throwing two different errors:

1. Msg 50000, Level 16, State 1, Line 1
   Merge statements with a WHEN NOT MATCHED [BY TARGET] clause must target a hash 
   distributed table.
2. Can not update ID column

But the same above query works fine when I use normal On-Prem SQL Server or Azure SQL Database.

In the above example table_A is a small table and contains < 1000 data. As per Microsoft recommendation we should use Hash distribution only when there are huge records.

So what could be the best approach?

Secondly, even if I am not updating the ID column, why the 2nd error is coming?

Any expert advice would be helpful.

score 0 · Answer 1 · answered Jul 30 '23 at 04:14

UPDATE table_A
SET milageRun = SOURCE.odometerCount
FROM table_A AS TARGET
JOIN table_B AS SOURCE
ON TARGET.ClientID = SOURCE.ClientID;

using a separate UPDATE statement without involving the IDENTITY column

INSERT INTO table_A (ClientID, milageRun)
SELECT ClientID, odometerCount
FROM table_B
WHERE NOT EXISTS (
    SELECT 1
    FROM table_A
    WHERE table_A.ClientID = table_B.ClientID
);

Azure Synapse SQL Pool: Issue in MERGE INSERT UPDATE statement

1 Answers1