SAP HANA中CTE后执行DELETE语句报语法错误，求正确语法

问题场景 #

编写包含两个通用CTE的SAP HANA SQL查询，执行针对TABLE_A的DELETE语句时触发语法错误，但将DELETE替换为SELECT可正常执行。

报错脚本 #

WITH CTE_A AS
(
  SELECT COL_1 FROM TABLE_A WHERE
  CONDITION1 = 'ABC'
  AND CONTITION2 = 'XYZ' -- 注意：此处存在拼写错误，应为CONDITION2
),

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

错误信息 #

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

正确语法方案 #

方案1：使用DELETE...USING关联CTE #

SAP HANA支持通过USING子句将CTE与DELETE语句关联，这是更符合HANA语法规范的写法：

WITH CTE_A AS
(
  SELECT COL_1 FROM TABLE_A WHERE
  CONDITION1 = 'ABC'
  AND CONDITION2 = 'XYZ' -- 修正拼写错误
),
CTE_B AS
(
  SELECT COL_2 FROM TABLE_A WHERE
  CONDITION3 = 'DEF'
  AND COL_1 IN (SELECT COL_1 FROM CTE_A)
)
DELETE FROM TABLE_A
USING CTE_B
WHERE TABLE_A.ACCOUNT_NO = CTE_B.COL_2;

方案2：将CTE嵌套到DELETE的子查询中 #

如果版本不支持USING，可将CTE嵌套在DELETE的WHERE子查询内部：

DELETE FROM TABLE_A
WHERE ACCOUNT_NO IN (
  SELECT COL_2 FROM (
    WITH CTE_A AS
    (
      SELECT COL_1 FROM TABLE_A WHERE
      CONDITION1 = 'ABC'
      AND CONDITION2 = 'XYZ'
    )
    SELECT COL_2 FROM TABLE_A WHERE
    CONDITION3 = 'DEF'
    AND COL_1 IN (SELECT COL_1 FROM CTE_A)
  ) AS CTE_B
);

补充说明 #

原脚本中的CONTITION2为拼写错误，修正后可避免潜在的逻辑错误。

内容的提问来源于stack exchange，提问作者vams