具有子查询因子的Oracle DELETE语句 [英] Oracle DELETE statement with subquery factoring

问题描述

尝试执行此操作(在SQL Server中有效):

Trying to do this (works in SQL Server):

WITH X AS (), Y AS (), Z AS ()
DELETE FROM TBL
WHERE TBL.ID IN (SELECT ID FROM Z);

这在Oracle中有效:

This works in Oracle:

WITH X AS (), Y AS (), Z AS ()
SELECT * FROM TBL
WHERE TBL.ID IN (SELECT ID FROM Z);

但是DELETE不能:ORA-00928:缺少SELECT关键字

But the DELETE does not: ORA-00928: missing SELECT keyword

我的子查询很大，是否有其他语法可以使它正常工作?

My subqueries are rather large, is there a different syntax to get this to work?

推荐答案

除SELECT语句外，不能将子查询分解/CTE与其他任何内容一起使用. 摘自文档:

You cannot use Subquery Factoring/CTE with anything but the SELECT statement. From the documentation:

您可以在任何 顶级SELECT语句，在大多数情况下 子查询的类型.

You can specify this clause in any top-level SELECT statement and in most types of subqueries.

您可以这样做:

DELETE FROM tbl WHERE tbl.id IN
(WITH X AS (), Y AS (), Z AS ()
SELECT id FROM TBL
 WHERE TBL.ID IN (SELECT ID FROM Z));

这篇关于具有子查询因子的Oracle DELETE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！