CTE Functionality followed by Delete statement fails with Syntax error

Question

SAP HANA SQL 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. 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?

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);

Answer 1

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'
                                         )
                        )
                     ) ;