DBR 11.2 ML中MERGE语句WHEN NOT MATCHED BY SOURCE语法报错求助

你的MERGE语句报错是因为**WHEN NOT MATCHED BY SOURCE语法在Databricks Runtime (DBR) 11.2版本中尚未支持**，该特性是在DBR 12.0及以上版本才正式引入的，这就是为什么你对照最新官方文档编写的语句在旧版本环境中无法运行的核心原因。

解决方案1：升级Databricks Runtime版本 #

如果集群权限和业务条件允许，直接将集群升级到DBR 12.0或更高版本，原MERGE语句即可正常执行。

解决方案2：拆分逻辑为MERGE + 单独UPDATE（适配DBR 11.2） #

在DBR 11.2中，无法通过单条MERGE语句实现“源表无匹配时更新目标表”的逻辑，需要拆分两步操作：

1. 先执行原MERGE语句的匹配更新和插入逻辑（移除WHEN NOT MATCHED BY SOURCE分支）：

MERGE INTO warehouse.pdr_debit_card as TARGET
USING (SELECT * FROM (
  SELECT CIF, 
      CARD_TYPE,
      ISSUE_DATE,
      MATURITY_DATE,
      BOO,
      DATA_DATE, 
      row_number() over (PARTITION BY CIF, 
        CARD_TYPE,
        ISSUE_DATE,
        MATURITY_DATE,
        BOO,
        DATA_DATE order by cif) as rank
      from staging.pdr_debit_card
      ) s where s.rank = 1 ) as SOURCE
ON TARGET.CIF = SOURCE.CIF AND
TARGET.CARD_TYPE = SOURCE.CARD_TYPE AND
TARGET.ISSUE_DATE = SOURCE.ISSUE_DATE AND
TARGET.MATURITY_DATE = SOURCE.MATURITY_DATE AND
TARGET.DATA_DATE = SOURCE.DATA_DATE AND
TARGET.BOO = SOURCE.BOO 
WHEN MATCHED THEN
  UPDATE SET
    STATUS = 'active'
WHEN NOT MATCHED THEN
  INSERT (
    CIF,
    CARD_TYPE,
    ISSUE_DATE,
    MATURITY_DATE,
    DATA_DATE,
    BOO,
    STATUS
  )
  VALUES (
    cast(SOURCE.CIF as string),
    cast(SOURCE.CARD_TYPE as string),
    to_date(SOURCE.ISSUE_DATE, 'yyyy-MM-dd'),
    to_date(SOURCE.MATURITY_DATE, 'yyyy-MM-dd'),
    to_date(SOURCE.DATA_DATE, 'yyyyMMdd'),
    cast(SOURCE.BOO as string),
    'active'
  );

2. 再执行单独的UPDATE语句，将源表中不存在的active状态记录置为inactive：

UPDATE warehouse.pdr_debit_card
SET STATUS = 'inactive'
WHERE STATUS = 'active'
AND NOT EXISTS (
  SELECT 1 FROM (
    SELECT CIF, 
        CARD_TYPE,
        ISSUE_DATE,
        MATURITY_DATE,
        BOO,
        DATA_DATE, 
        row_number() over (PARTITION BY CIF, 
          CARD_TYPE,
          ISSUE_DATE,
          MATURITY_DATE,
          BOO,
          DATA_DATE order by cif) as rank
        from staging.pdr_debit_card
        ) s where s.rank = 1
  AND warehouse.pdr_debit_card.CIF = s.CIF 
  AND warehouse.pdr_debit_card.CARD_TYPE = s.CARD_TYPE 
  AND warehouse.pdr_debit_card.ISSUE_DATE = s.ISSUE_DATE 
  AND warehouse.pdr_debit_card.MATURITY_DATE = s.MATURITY_DATE 
  AND warehouse.pdr_debit_card.DATA_DATE = s.DATA_DATE 
  AND warehouse.pdr_debit_card.BOO = s.BOO
);

优化提示 #

重复的子查询可以通过创建临时视图来简化，提升代码可维护性：

CREATE OR REPLACE TEMP VIEW staging_debit_card_deduped AS
SELECT CIF, 
    CARD_TYPE,
    ISSUE_DATE,
    MATURITY_DATE,
    BOO,
    DATA_DATE
FROM (
  SELECT *,
    row_number() over (PARTITION BY CIF, CARD_TYPE, ISSUE_DATE, MATURITY_DATE, BOO, DATA_DATE order by cif) as rank
  from staging.pdr_debit_card
) s 
where s.rank = 1;

之后的MERGE和UPDATE语句直接引用staging_debit_card_deduped视图即可。

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