如何用另一张表数据更新Snowflake表并通过SELECT得到预期结果

实现方案 #

你的需求包含两部分逻辑：两表Date/columnA/columnB/reff完全匹配时，用TABLE2的sum值覆盖TABLE1的对应值；同时保留TABLE1未匹配的行、追加TABLE2未匹配的行，可根据是否需要修改原表选择对应方案： #

方案1：仅查询得到目标结果，不修改原表 #

使用全外连接加字段逻辑判断即可直接返回预期结果：

SELECT
  COALESCE(t1.Date, t2.Date) AS Date,
  COALESCE(t1.columnA, t2.columnA) AS columnA,
  COALESCE(t1.columnB, t2.columnB) AS columnB,
  CASE WHEN t2.Date IS NOT NULL THEN t2.sum ELSE t1.sum END AS sum,
  COALESCE(t1.reff, t2.reff) AS reff
FROM TABLE1 t1
FULL OUTER JOIN TABLE2 t2
  ON t1.Date = t2.Date
  AND t1.columnA = t2.columnA
  AND t1.columnB = t2.columnB
  AND t1.reff = t2.reff
ORDER BY columnA, columnB;

方案2：实际修改TABLE1表（更新匹配行+插入不匹配行） #

用Snowflake原生支持的MERGE语法，一步完成更新和插入操作：

MERGE INTO TABLE1 t1
USING TABLE2 t2
  ON t1.Date = t2.Date
  AND t1.columnA = t2.columnA
  AND t1.columnB = t2.columnB
  AND t1.reff = t2.reff
-- 匹配到的行用TABLE2的sum更新TABLE1
WHEN MATCHED THEN
  UPDATE SET t1.sum = t2.sum
-- 未匹配到的行直接把TABLE2的行插入TABLE1
WHEN NOT MATCHED THEN
  INSERT (Date, columnA, columnB, sum, reff)
  VALUES (t2.Date, t2.columnA, t2.columnB, t2.sum, t2.reff);

执行完成后查询TABLE1即可得到预期结果：

SELECT * FROM TABLE1 ORDER BY columnA, columnB;

内容的提问来源于stack exchange，提问作者Py1996