2

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.

2
  • FWIW, the MERGE keyword is known to have a multitude of bugs (some of which Microsoft has acknowledged and decided won't be fixed) and should be avoided.
    – J.D.
    Jul 16, 2022 at 3:43
  • I havent seen the IF NOT MATCHED construction before, is that an MS extension? Jul 16, 2022 at 4:13

1 Answer 1

0
UPDATE table_A
SET milageRun = SOURCE.odometerCount
FROM table_A AS TARGET
JOIN table_B AS SOURCE
ON TARGET.ClientID = SOURCE.ClientID;
  1. 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
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.