- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 07:39 PM
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
- Labels:
-
DBR
-
Delta
-
SQL Statement
-
Syntax
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 08:32 PM
I have figured this out, WHEN NOT MATCHED BY TARGET support Databricks Runtime 12.1 and above
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 08:32 PM
I have figured this out, WHEN NOT MATCHED BY TARGET support Databricks Runtime 12.1 and above
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-23-2023 11:17 PM
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)