How to delete rows using CTE and INNER JOIN?

Question

How to delete data from a table using CTE and INNER JOIN? Is this a valid syntax, so this should 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 CTE nor INNER JOIN are valid for the DELETE command. The same applies to the INSERT and UPDATE .

Usually 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 remove from the results of a subquery. This is described (albeit slightly) in the documents .

---

Appendix Also see @Gerrat's answer, which shows how to use CTE in a DELETE … WHERE … IN query. There are times when this approach will be more useful than my answer.

Answer 2

The correct answer is incorrect, REMOVE using CTE (same with INSERT and UPDATE commands).
( You cannot use the inner join, but you can use the CTE with DELETE ).

In Oracle 9i +, the following applies:

 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 does not benefit from the CTE at all, but other, more complex statements might.

Answer 3

try the following approach: -

 delete from table_b b where exists ( with my_cte as ( select distinct var1, var2 from table_a ) select 1 from my_cte a where a.var1 = b.datecol and a.var2 = b.mycol;