CODEWITHSUNDEEP
sqloracle-databasecommon-table-expressiondelete-rowsql-delete
Matkrupp
Matkrupp

Reputation: 785

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;

Upvotes: 6

Views: 13417

Answers (4)

AddicksAddict
AddicksAddict

Reputation: 21

I had to delete records from table1 by referencing two other tables, 2 and 3, so did this:

DELETE
  FROM table1
 WHERE table1.var1 = :parameter1
   AND table1.var2 || table1.var3 IN
   (WITH my_cte
      AS (SELECT table2.var3
            FROM table2
           WHERE table2.var1 > :parameter2
             AND table2.var2 = constant1
             AND :parameter2 >= (SELECT
                                MAX(table3.var1)
                                   FROM table3
                                  WHERE
                                     table3.var2
                                   = table2.var3
                                   )
         )
    SELECT table3.var3 || table3.var4
            FROM table3
           INNER JOIN my_cte
              ON (table3.var2 = my_cte.var1)
   )

for anyone who's interested, on SQLServer, the order of the various clauses has to be different:

WITH my_cte
  AS (SELECT table2.var3
        FROM table2
       WHERE table2.var1 > :parameter2
         AND table2.var2 = constant1
     AND :parameter2 >= (SELECT MAX(table3.var1)
                           FROM table3
                          WHERE table3.var2
                                = table2.var3
                        )
     )
DELETE
  FROM table1
  WHERE table1.var1 = :parameter1
    AND table1.var2 || table1.var3 IN
        (SELECT table3.var3 || table3.var4
           FROM table3
          INNER JOIN my_cte
             ON (table3.var2 = my_cte.var1)
        )

Upvotes: -1

Ed Gibbs
Ed Gibbs

Reputation: 26363

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.

Upvotes: 9

Punit Soneji
Punit Soneji

Reputation: 11

try below 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;

Upvotes: 0

Gerrat
Gerrat

Reputation: 29710

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.

Upvotes: 8

Related Questions