Databricks

Kajorn · ‎01-23-2023

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