merge statement using a cte throwing an error

August 9, 2022

I’m attempting to convert a MSSQL Query into Snowflake and I’m getting tripped up when I’m attempting to update my table using a secondary CTE after applying some business logic.

CREATE TEMPORARY TABLE test
(
  id  integer,
  src text,
  val text

);
INSERT INTO test

SELECT t.*
FROM (
  VALUES (1, 'A', 'AA')
       , (1, 'A', 'AB') t(id, src, val)
  )

SELECT * FROM TEST

+--+---+---+
|ID|SRC|VAL|
+--+---+---+
|1 |A  |AA |
|1 |A  |AB |
+--+---+---+

WITH dat AS (
  SELECT id, src, val
  , ROW_NUMBER() OVER(ORDER BY id) rn
  FROM test
              )

, src AS (
  SELECT id, src, val,
         MD5(
           ARRAY_TO_STRING(
             ARRAY_CONSTRUCT(id,src,val), ':'
             )
           ) AS CHANGE_HASH
  FROM dat
  WHERE rn = 1
           )

MERGE INTO target as tgt using src -- <-- this throws an error 
  ON src.id = tgt.id 
  AND src.CHANGE_HASH != tgt.CHANGE_HASH
THEN UPDATE 
SET (
  ...
       )

the error I get is

[42000][1003] SQL compilation error: syntax error line 61 at position 15 unexpected '<EOF>'.

Where is my mistake?

>Solution :

The CTE should be in the subquery like this:

MERGE INTO target as tgt using
(
  WITH dat AS (
  SELECT id, src, val
  , ROW_NUMBER() OVER(ORDER BY id) rn
  FROM test)
, src AS (
  SELECT id, src, val,
         MD5(
           ARRAY_TO_STRING(
             ARRAY_CONSTRUCT(id,src,val), ':'
             )
           ) AS CHANGE_HASH
  FROM dat
  WHERE rn = 1 )
select * from src) src
  ON src.id = tgt.id 
  AND src.CHANGE_HASH != tgt.CHANGE_HASH
WHEN MATCHED THEN UPDATE SET tgt.src = src.src;