如何在Snowflake中用SQL合并字面量表到现有表并解决标识符报错

DevOps 小满

2026-10-9

问题原因

作用域错误：你给VALUES子句设置的别名vals仅在子查询内部有效，MERGE语句的ON条件、UPDATE/INSERT分支属于外层作用域，无法直接访问子查询内部的别名
列名不匹配：你在子查询中定义的列名为col1/col2/col3，但后续UPDATE和INSERT分支中错误使用了不存在的colOne/colTwo/colThree作为列名，同时子查询中parse_json(col3)没有指定别名，会导致该列默认生成随机列名无法正常引用

修正后的代码

merge into existing_table
    using 
        (select col1, col2, parse_json(col3) as col3 from
            values 
            ('2021-08-03 17:38:53.977484+00:00', '34o234j3', $$ {"data":"here's some data","data2":"Here's some more data"} $$), 
            ('2021-08-02 08:38:55', '1934802h32', $$ {"data":"here's some dataX","data2":"Here's some more dataY"} $$)
            as vals_tmp(col1, col2, col3))
    as vals -- 给整个USING的子查询设置外层可访问的别名vals
    on existing_table.sales_order_id = vals.col2
when matched then 
    update set 
        existing_table.timestamp_utc = vals.col1, existing_table.JSON = vals.col3
when not matched then
    insert (timestamp_utc, sales_order_id, JSON)
    values (vals.col1, vals.col2, vals.col3);