CODEWITHSUNDEEP
sqldatabricksazure-databricksdatabricks-sql
Khlph
Khlph

Reputation: 11

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

I tried to execute the SQL statement below but the exception occurred.

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

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'

Based on this doc, the above statement did follow the syntax reference.

Or this syntax won't apply to DBR 11.2 ML?

I have tried to included WHEN NOT MATCHED BY TARGET and the exception was raised also.

Upvotes: 0

Views: 1430

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8382

I also tried with similar databricks runtime version getting same error. enter image description here

As per Microsoft document,Entries in the target table that are not matched by records in the source table can be updated or deleted using the WHEN NOT MATCHED BY SOURCE clause in Databricks Runtime 12.1 and above.

The work around or solution is to upgrade your databricks runtime version to 12.1 and above.

Now its executing fine: enter image description here

Upvotes: 2

Related Questions