CODEWITHSUNDEEP
common-table-expressionsql-deletehana
vams
vams

Reputation: 11

Why DELETE after CTE fails with Syntax error?

I have a SAP HANA SQL query with two Common CTE's and followed by delete SQL on a regular Table. The SQL execution fails with incorrect syntax. Below is the error code and the HANA SQl script.

WITH CTE_A AS
(
  SELECT COL_1 FROM TABLE_A WHERE
  CONDITION1 = 'ABC'
  AND CONTITION2 = 'XYZ'
),

     CTE_B AS
(
  SELECT COL_1, COL_2 FROM TABLE_A WHERE
  CONDITION3 = 'DEF'
  AND COL_1 IN (SELECT COL_1 FROM CTE_A)
)

DELETE FROM TABLE_A
WHERE ACCOUNT_NO IN (SELECT COL_2 FROM CTE_B);

Error:

(dberror) [257]: sql syntax error: incorrect syntax near "DELETE": line 64 col 0 (at pos 2273);

When replaced the 'DELETE FROM' to 'SELECT * Start', the SQL execution works as expected.

Could one of you please let us know the proper syntax to delete the records?

Upvotes: 1

Views: 147

Answers (1)

Satish Kumar
Satish Kumar

Reputation: 253

CTE's in SAP HANA only supports SELECT statements. UPDATE/ DELETE statements and recursive CTE's are not supported.

You can use subqueries to achieve the same functionality as shown below.

DELETE FROM TABLE_A
WHERE ACCOUNT_NO IN (SELECT COL_2 FROM 
                        ( SELECT COL_1, COL_2 FROM TABLE_A WHERE
                          CONDITION3 = 'DEF'
                           AND COL_1 IN ( SELECT COL_1 FROM TABLE_A WHERE
                                          CONDITION1 = 'ABC'
                                          AND CONTITION2 = 'XYZ'
                                         )
                        )
                     ) ;

Upvotes: 1

Related Questions