Snowflake如何从另一张表随机抽取值更新目标表指定列

你之前的UPDATE语句没有写关联条件，源表和目标表会生成笛卡尔积，每个目标行都会匹配到所有源表行，Snowflake会随机选一个源行给目标行赋值，所以会出现重复值，同时触发multi-joined rows updated提示。哪怕先在子查询采样5行，仍然是笛卡尔积逻辑，问题不会解决。

核心逻辑是给目标表和采样后的源表分别生成唯一临时行号，按行号一对一关联更新，完全不依赖主键、索引，适配大数据量场景。

方案1：标准UPDATE写法（适合需要增量更新的场景） #

以仅更新name字段为例：

UPDATE tmp_target t
SET t.name = s.name
FROM (
    -- 目标表生成临时行号
    SELECT *, ROW_NUMBER() OVER (ORDER BY 1) AS rn FROM tmp_target
) t_rn
INNER JOIN (
    -- 源表随机抽取和目标表行数相同的样本，生成临时行号
    SELECT name, ROW_NUMBER() OVER (ORDER BY 1) AS rn 
    FROM tmp_source SAMPLE ROW (5 ROWS)
) s
ON t_rn.rn = s.rn
-- 关联回原目标表，匹配所有字段避免重复行匹配误差
WHERE t.name = t_rn.name 
  AND t.age = t_rn.age 
  AND t.gender = t_rn.gender 
  AND t.zip = t_rn.zip;

如果需要同时更新多个字段（比如name+age），只需修改SELECT和SET部分的对应字段即可。

方案2：重写目标表写法（10万行级场景效率更高） #

Snowflake是列存储架构，整表更新场景下重写表的效率远高于逐行UPDATE，性能可以提升3~10倍：

CREATE OR REPLACE TEMPORARY TABLE tmp_target AS
SELECT 
    s.name,
    s.age, -- 需要更新的字段取源表值
    t.gender, -- 不需要更新的字段保留原表值
    t.zip
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY 1) AS rn FROM tmp_target
) t
INNER JOIN (
    SELECT name, age, ROW_NUMBER() OVER (ORDER BY 1) AS rn 
    FROM tmp_source SAMPLE ROW (5 ROWS)
) s
ON t.rn = s.rn;

• 完全消除非确定性更新问题：行号一对一匹配，不会出现重复值，也不会触发多连接更新提示
• 适配大数据量：Snowflake的SAMPLE算子是高效跳过采样，1000万行源表采样10万行耗时仅数百毫秒；重写表方案适合10万行级目标表的批量更新场景
• 无依赖：不需要表有主键、索引，也不需要提前新增持久化字段，所有行号都是查询时临时生成

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