带有子查询分解的 Oracle DELETE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6603171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle DELETE statement with subquery factoring
提问by Cade Roux
Trying to do this (works in SQL Server):
尝试这样做（适用于 SQL Server）：
WITH X AS (), Y AS (), Z AS ()
DELETE FROM TBL
WHERE TBL.ID IN (SELECT ID FROM Z);
This works in Oracle:
这适用于 Oracle：
WITH X AS (), Y AS (), Z AS ()
SELECT * FROM TBL
WHERE TBL.ID IN (SELECT ID FROM Z);
But the DELETE does not: ORA-00928: missing SELECT keyword
但 DELETE 没有：ORA-00928：缺少 SELECT 关键字
My subqueries are rather large, is there a different syntax to get this to work?
我的子查询相当大，是否有不同的语法来使其工作？
回答by DCookie
You cannot use Subquery Factoring/CTE with anything but the SELECT statement. From the documentation:
除了 SELECT 语句之外，您不能将子查询因子分解/CTE 与任何东西一起使用。从文档：
You can specify this clause in any top-level SELECT statement and in most types of subqueries.
您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。
You could do this:
你可以这样做：
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));
回答by Cade Roux
I got this to work (which I'm sure doesn't work in SQL Server):
我让它工作（我确定它在 SQL Server 中不起作用）：
DELETE FROM TBL
WHERE TBL.ID IN (
WITH X AS (), Y AS (), Z AS ()
SELECT ID FROM Z
);
回答by cwallenpoole
Well, at a minimum, you need to have all of the aliased queries appear in the FROM statement somehow. I don't know if there are more issues, but that is a must (and I believe that 00928 is the error that happens when you don't).
嗯，至少，您需要让所有别名查询以某种方式出现在 FROM 语句中。我不知道是否还有更多问题，但这是必须的（而且我相信 00928 是您不这样做时发生的错误）。
