Spark SQL如何使用MERGE更新倒数第二条记录并插入新行

你原有写法的匹配逻辑不符合业务需求，该条件仅能匹配和新传入数据完全一致的存量记录，既无法定位到需要更新的尾行，也不能触发新数据插入。

实现前提 #

先将新传入的ID、新发票ID构造为临时视图/数据集new_data，包含2个字段：

• new_id：本次传入的新ID值，比如样例中为122
• new_invoice_id：本次传入的新发票ID，比如样例中为IN-006

完整MERGE语句 #

MERGE INTO table_name tn
USING (
  -- 构造两份源数据：一份用于匹配更新尾行，一份用于触发新行插入
  SELECT
    'UPDATE' AS op_type,
    NULL AS new_id,
    new_invoice_id,
    -- 定位当前表唯一的尾记录（next_invoice_id为null的行）
    (SELECT Invoice_id FROM table_name WHERE next_invoice_id IS NULL) AS match_invoice_id
  FROM new_data
  UNION ALL
  SELECT
    'INSERT' AS op_type,
    new_id,
    new_invoice_id,
    -- 插入行不匹配任何存量记录，匹配字段设为null
    NULL AS match_invoice_id
  FROM new_data
) nd
ON tn.Invoice_id = nd.match_invoice_id
-- 匹配到尾记录时，更新其next_invoice_id为新发票ID
WHEN MATCHED THEN
  UPDATE SET tn.next_invoice_id = nd.new_invoice_id
-- 插入行无匹配存量记录时，插入新行
WHEN NOT MATCHED THEN
  INSERT (ID, Invoice_id, next_invoice_id)
  VALUES (nd.new_id, nd.new_invoice_id, NULL);

执行逻辑说明 #

• 匹配阶段：源数据中标记为UPDATE的行会通过match_invoice_id精准命中原表中next_invoice_id为null的尾记录，其余存量行都不会被匹配，不会产生误更新
• 匹配成功时：将尾记录的next_invoice_id更新为本次传入的新发票ID，完成第一个业务要求
• 未匹配阶段：源数据中标记为INSERT的行因为match_invoice_id为null，tn.Invoice_id = null的判断结果恒为不成立，不会关联到任何存量行，会触发插入逻辑，写入ID为新ID、发票ID为新发票ID、next_invoice_id为null的新记录，完成第二个业务要求

注意事项 #

• 该写法适配你给出的单发票链场景，即全表有且仅有一条next_invoice_id IS NULL的尾记录。如果存在多链场景（比如按业务维度拆分独立发票链），只需要在查询match_invoice_id的子查询中增加对应的维度过滤条件即可。
• 正式执行前可以单独执行USING后的子查询，确认返回两行数据：一行为UPDATE类型，携带正确的尾记录发票ID；一行为INSERT类型，携带本次传入的新ID和新发票ID。

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