Why DELETE after CTE fails with Syntax error?

Question

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?

Satish Kumar · Accepted Answer · 2024-01-23 22:37:20Z

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

answered Jan 23, 2024 at 22:37

Satish Kumar

3632 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Satish Kumar Over a year ago

Similar topic has been discussed before in stack link: stackoverflow.com/questions/42487648/…

Satish Kumar Over a year ago

I think another option is to create temporary views as discussed in above link. The solution depends on if you need to use the temporary results anywhere else, other than the delete query.

Collectives™ on Stack Overflow

Why DELETE after CTE fails with Syntax error?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related