Hi All,
Can the Deletes be used in place of SELECT in CTE ?
(below code snippet is a sample)
--select
with cte_sample as (select date from example)
SELECT * from calendar_extract where date in (select date from cte_sample);
--delete
with cte_sample as (select date from example)
delete from calendar_extract where date in (select date from cte_sample);
I'm using 19c and I get the error ORA-00928: missing SELECT keyword
DELETE is the first keyword in this statement, so you can't put WITH before it.
You can delete the results of a query, so you can use WITH inside delete, e.g.:
create table t as
select level c1 from dual
connect by level <= 100;
delete (
with evens as (
select level * 2 c1 from dual
connect by level <= 50
)
select * from t
where c1 in ( select c1 from evens )
);
-- 50 rows deleted