Redshift含EXISTS子句的DELETE脚本报错，SELECT查询正常

问题原因 #

Redshift的语法规则中，DELETE语句里嵌套在EXISTS子查询内的WITH公共表达式无法引用外部DELETE语句的表别名，而SELECT语句的执行逻辑允许这种跨层级的别名引用，这就是为什么逻辑相同的SELECT能正常运行，DELETE却报语法错误的核心原因。

解决方法 #

将WITH子句从EXISTS子查询中移到DELETE语句的最外层，让公共表达式处于和DELETE主语句同一层级，这样就能正常引用外部表别名。

修改后的DELETE脚本（方案一） #

WITH CCDA as (
SELECT
row_number() OVER (PARTITION BY emp_id,customer_id ORDER BY seq_num desc) rn
, emp_id, customer_id
FROM TBL_2
WHERE end_dt > (SELECT max(end_dt) FROM TBL_3)
)
DELETE FROM TBL_1 stg
WHERE EXISTS (
SELECT 1 FROM CCDA 
WHERE rn = 1
AND stg.emp_id = CCDA.emp_id
AND stg.customer_id = CCDA.customer_id
);

简化写法（方案二） #

也可以直接将WITH子句的结果作为关联条件，避免EXISTS嵌套，执行效率更直观：

WITH CCDA as (
SELECT
emp_id, customer_id
FROM (
    SELECT
    row_number() OVER (PARTITION BY emp_id,customer_id ORDER BY seq_num desc) rn
    , emp_id, customer_id
    FROM TBL_2
    WHERE end_dt > (SELECT max(end_dt) FROM TBL_3)
) t
WHERE rn = 1
)
DELETE FROM TBL_1 stg
USING CCDA
WHERE stg.emp_id = CCDA.emp_id
AND stg.customer_id = CCDA.customer_id;

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