如何使用CTE和INNER JOIN删除行？
如何使用CTE和INNER JOIN删除行？
提问于 2013-05-21 19:24:47
如何使用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;回答 2
Stack Overflow用户
发布于 2018-01-25 20:01:25
尝试下面的方法：
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;Stack Overflow用户
发布于 2021-05-18 20:14:53
我不得不通过引用另外两个表2和3从table1中删除记录，所以这样做了：
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)
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16668806
复制相关文章
相似问题