Snowflake SQL：通过Payment表关联填充Transaction表空CID值

以下是实现需求的SQL语句，通过关联Payment表的hash值，将Transaction表中NULL的cid字段更新为同hash对应的已填充cid值：

WITH hash_to_cid AS (
    -- 建立cardhash到有效cid的映射（确保每个hash对应唯一非空cid）
    SELECT 
        p.cardhash,
        MAX(t.cid) AS target_cid
    FROM payment p
    JOIN transaction t ON p.tid = t.tid
    WHERE t.cid IS NOT NULL 
      AND p.cardhash IS NOT NULL
    GROUP BY p.cardhash
)
UPDATE transaction t_update
SET cid = h.target_cid
FROM payment p
JOIN hash_to_cid h ON p.cardhash = h.cardhash
WHERE t_update.tid = p.tid
  AND t_update.cid IS NULL
  AND p.cardhash IS NOT NULL;

逻辑说明： #

1. CTE hash_to_cid：
   • 关联Payment表和Transaction表，筛选出Transaction表中cid非空的记录
   • 按cardhash分组，通过MAX()函数确保每个hash对应唯一的cid值（假设同一hash下的cid一致）
2. UPDATE语句：
   • 针对Transaction表中cid为NULL的记录，通过Payment表关联到对应的cardhash
   • 匹配到hash_to_cid中的有效cid后进行更新
   • 过滤掉Payment表中cardhash为NULL的情况，避免无效匹配

测试数据验证： #

执行上述语句后，Transaction表的更新结果如下：

• TID=123: cid变为cid_2（匹配hash=3333对应的TID=770的cid）
• TID=456: cid变为cid_1（匹配hash=1111对应的TID=880的cid）
• TID=234: cid变为cid_2（匹配hash=3333对应的TID=770的cid）
• 其余cid为NULL的记录（如789、312等）因无对应有效hash或cid，保持NULL不变

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