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: A more common name for subquery factoring is the SQL WITH clause, an ANSI-99 standard keyword. The main purpose of Oracle subquery factoring is to simplify complex SQL. Subquery factoring simplifies complex SQL by materializing complex SQL in much the same way as global temporary tables.
Example - Using EXISTS with the DELETE Statement You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.
The WITH clause allows you, as part of your select statement, to assign a name to a subquery and utilise its results by referencing that name. It is, on first glance, quite jarring. Because the subquery factoring clause brutally transforms the look of a query, making it no longer start with the SELECT keyword.
You cannot delete rows from a read-only materialized view. If you delete rows from a writable materialized view, then the database removes the rows from the underlying container table. However, the deletions are overwritten at the next refresh operation.
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
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With