How to delete rows using CTE and INNER JOIN?

Question

How can I delete data from a table using CTE and INNER JOIN? Is this valid syntax, so should this work:

with my_cte as (
select distinct var1, var2
from table_a
)
delete  
from table_b b inner join my_cte 
  on var1 = b.datecol and var2 = b.mycol;

Answer 1

In Oracle neither the CTE nor the INNER JOIN are valid for the DELETE command. The same applies for the INSERT and UPDATE commands.

Generally the best alternative is to use DELETE ... WHERE ... IN:

DELETE FROM table_b
WHERE (datecol,  mycol) IN (
  SELECT DISTINCT var1, var2 FROM table_a)

You can also delete from the results of a subquery. This is covered (though lightly) in the docs.

---

Addendum Also see @Gerrat's answer, which shows how to use the CTE within the DELETE … WHERE … IN query. There are cases where this approach will be more helpful than my answer.

Answer 2

Ed's answer is incorrect , w.r.t. the DELETE with a CTE (ditto with the INSERT and UPDATE commands).
(You can't use an inner join, but you can use a CTE with DELETE).

The following is valid in Oracle 9i+:

DELETE FROM table_b WHERE (datecol, mycol) IN (
    WITH my_cte AS (
        SELECT DISTINCT var1, var2
        FROM table_a
    )
    SELECT var1, var2 from my_cte
);

This particular case doesn't benefit at all from the CTE, but other, more complicated statements could.