merge statement using a cte throwing an error

user_9375102 0
Viewed 58

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?

snowflake-cloud-data-platform
2 Answers

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;
user_125509650

The CTE is no needed as the entire section could be simplified with single QUALIFY:

SELECT id, src, val,
         MD5(
           ARRAY_TO_STRING(
             ARRAY_CONSTRUCT(id,src,val), ':'
             )
           ) AS CHANGE_HASH
FROM dat
QUALIFY ROW_NUMBER() OVER(ORDER BY id) = 1

is the same as:

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
           )

and MERGE becomes:

MERGE INTO target as tgt 
USING (
        SELECT id, src, val,
             MD5(
               ARRAY_TO_STRING(
                 ARRAY_CONSTRUCT(id,src,val), ':'
                 )
               ) AS CHANGE_HASH
        FROM dat
        QUALIFY ROW_NUMBER() OVER(ORDER BY id) = 1
      ) AS src
  ON ...
...;
user_50708790
Related