Search code examples
sqloracle-databasecommon-table-expressiondelete-rowsql-delete

How to delete rows using CTE and INNER JOIN?

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;
Solution

  • 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.