从CTE中删除并连接

Question

我正在尝试使用SQL表来实现一个先进先出（FIFO）队列。

我有以下的SQL代码（为了发布做了修改），连接和参数的使用对于这个过程的工作方式非常重要。

With cte as (
   select top(1) q.* from queue q with (readpast)
   inner join MyTable a on q.id = a.myTableID AND myTable.procID = @myParam
   order by q.Data asc
)
delete from cte
    output
      deleted.ID,
      deleted.col1

运行这个语句会返回一个错误：“视图或函数 'cte' 无法更新，因为修改会影响多个基本表。”

我理解为什么会出现这个错误，但我无法弄清楚如何修复它。任何建议将不胜感激！

- Jay

Answer 1

你可以在CTE中使用exists()代替与MyTable的内连接。

with cte as 
(
  select top(1) q.id,
                q.col1
  from queue q with (readpast)
  where exists(
              select *
              from  MyTable a 
              where  q.id = a.myTableID AND 
                     a.procID = @myParam
              )
  order by q.Data asc
)
delete from cte
output deleted.ID, deleted.col1;

- Mikael Eriksson

---

这种方法比Kieninger提供的示例更好的地方在于，它提供了一种一致的方法来构建DELETE命令，您可以首先测试SELECT的结果，以查看DELETE from cte将影响哪些行。 - Derek Greer

Answer 2

像这样吗？

With cte as (
   select top(1) q.* from queue q with (readpast)
   inner join MyTable a on q.id = a.myTableID AND myTable.procID = @myParam
   order by q.Data asc
)
delete from queue
Where ID in (Select Id from cte)

- sarin

---

从CTE中删除是完全可能的，SQL Server会从SELECT语句中推断出底层表。但我的问题是我正在另一张表上进行连接，因此要删除的表现在是不明确的。我会对您的答案进行一些测试，这里并发将是一个问题，所以我担心在IN语句周围进行锁定。 - Jay

Answer 3

使用CTE在此处感觉有些勉强。您可以简单地：

 DELETE FROM [queue]
  WHERE id IN ( 
    SELECT TOP 1 
           q.id 
      FROM [queue] q WITH (READPAST)
           INNER JOIN 
           MyTable a ON q.id = a.myTableID 
                    AND myTable.procID = @myParam
    ORDER BY q.Data ASC)

如果您想使用CTE，我喜欢@sarin的答案，但是可以使用EXIST代替：

WITH cte AS (
  SELECT TOP 1 
         q.id 
    FROM [queue] q WITH (READPAST)
         INNER JOIN 
         MyTable a ON q.id = a.myTableID 
                  AND myTable.procID = @myParam
   ORDER BY q.Data ASC
)
DELETE [queue] 
 WHERE EXISTS(SELECT 1 FROM cte WHERE cte.id = [queue].id)

- Karl Kieninger

Answer 4

我有同样的问题，但我想删除其余的行，但它没有成功。任何帮助都将产生巨大的影响 :)

WITH CTE AS( SELECT date,dd, pp, ROW_NUMBER() OVER (PARTITION BY date ORDER BY dd , dd desc ) AS RN FROM my_table WHERE date between "2023-01-04" and "2023-04-05") Select * from CTE DELETE WHERE RN < 2;

- rekas

---

这并没有真正回答问题。如果您有不同的问题，可以通过点击提问来提出。如果您想在此问题获得新的答案时得到通知，您可以关注此问题。一旦您拥有足够的声望，您还可以添加悬赏以吸引更多关注。- 来自审核 - wibeasley