Oracle DELETE statement with subquery factoring

Question

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

This works in 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

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

Answer 1

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

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

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