我正试图创建一个合并语句,在列DATE
为>=之前,将所有值保留在FINAL_TABLE
中今天的日期,并从我的LANDING_TABLE
中插入今天日期的新值。
DELETE和INSERT语句的工作示例如下所示:
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()
我希望保留FLATTEN语句和WHERE条件,同时将整个语句放在一个MERGE语句中。
是否可以或者应该先用我想要插入的值创建一个临时表,然后在merge语句中使用它?
MERGE语句可以使用子查询/cte作为源:
MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]
来源:
指定要与目标表联接的表或子查询。
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 ...;