Creating a merge statement from a conditional INSERT select

user_7615751 0
Viewed 31

I'm trying to create a merge statement where I keep all the values in my FINAL_TABLE before the column DATE is >= today's date, and insert new values from today's date from my LANDING_TABLE.

The working example with a DELETE and INSERT statement can be seen here:

DELETE FROM FINAL_TABLE
WHERE "DATE" >= CURRENT_DATE()

INSERT INTO FINAL_TABLE
    SELECT X, Y.value :: string AS Y_SPLIT, "DATE", "PUBLIC"
    FROM LANDING TABLE, LATERAL FLATTEN (INPUT => STRTOK_TO_ARRAY(LANDING_TABLE.column, ', '), OUTER => TRUE) y
    WHERE "PUBLIC" ILIKE 'TRUE' AND "DATE" >= CURRENT_DATE()

I'd like to keep the FLATTEN statement and the WHERE conditions while having the whole statement in a single MERGE statement.

Is it possible or should I first create a temporary table with the values I want to insert and then use that in the merge statement?

snowflake-cloud-data-platform
1 Answers

The MERGE statement could use subqueries/cte as source:

MERGE INTO <target_table> USING <source> 
 ON <join_expr> { matchedClause | notMatchedClause } [ ... ]

source:

Specifies the table or subquery to join with the target table.

MERGE INTO FINAL_TABLE
USING (
  SELECT X, Y.value :: string AS Y_SPLIT, "DATE" AS col1, "PUBLIC" AS col2
  FROM LANDING TABLE
  ,LATERAL FLATTEN(INPUT=>STRTOK_TO_ARRAY(LANDING_TABLE.column, ', '), OUTER=>TRUE) y
   WHERE "PUBLIC" ILIKE 'TRUE' AND "DATE" >= CURRENT_DATE()
) AS SRC
  ON ...
WHEN ...;
user_50708790
Related