WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML)

Kajorn — Tue, 24 Jan 2023 03:39:46 GMT

Hi, I have trouble with executing the given SQL Statement below.

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 BY TARGET
  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'
  )
WHEN NOT MATCHED BY SOURCE AND STATUS = 'active' THEN 
  UPDATE SET
    STATUS = 'inactive';

Exception occurred,

ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'BY'(line 26, pos 17)
 
WHEN NOT MATCHED BY SOURCE AND STATUS = 'active' THEN 
-----------------^^^
  UPDATE SET
    STATUS = 'inactive'

My work runs on DBR 11.2 ML, Spark 3.3.0, Scala 2.12.

Based on the documentation, would this syntax apply on my statements?

Also, can Delta API work on this query too?

Thanks

Re: WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML)

Kajorn — Tue, 24 Jan 2023 04:32:17 GMT

I have figured this out, WHEN NOT MATCHED BY TARGET support Databricks Runtime 12.1 and above

Re: WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML)

Debayan — Tue, 24 Jan 2023 07:17:53 GMT

Hi, Please refer: https://docs.databricks.com/sql/language-manual/delta-merge-into.html

topic WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML) in Data Engineering

WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML)

Re: WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML)

Re: WHEN NOT MATCHED BY SOURCE Syntax error at or near 'BY' (DBR 11.2 ML)