如何使用CTE和INNER JOIN删除行

Question

如何使用CTE和INNER JOIN从表中删除数据？这是有效的语法吗，所以应该这样做：

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

在Oracle中，CTE和INNER JOIN对于DELETE命令都无效。这同样适用于INSERT和UPDATE命令。

通常，最好的选择是使用DELETE ... WHERE ... IN:

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

您也可以从子查询的结果中删除。这在文档中有介绍(尽管不多(。

---

附录另请参阅@Gerrat的回答，其中显示了如何在DELETE … WHERE … IN查询中使用CTE。在某些情况下，这种方法会比我的答案更有帮助。

Answer 2

Ed的回答不正确，请使用CTE删除DELETE(与INSERT和UPDATE命令相同(
(不能使用内部联接，但可以使用带DELETE的CTE(。

以下内容在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
);

这种特殊的情况根本没有从CTE中受益，但其他更复杂的语句可以。

Answer 3

尝试以下方法：-

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;

Answer 4

我不得不通过引用另外两个表2和3从表1中删除记录，所以这样做了：

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)
   )

对于任何感兴趣的人来说，在SQLServer上，各种子句的顺序必须不同：

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)
        )