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?
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));
I got this to work (which I'm sure doesn't work in SQL Server):
DELETE FROM TBL
WHERE TBL.ID IN (
WITH X AS (), Y AS (), Z AS ()
SELECT ID FROM Z
);
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).
