使用join从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

运行此语句将返回错误'View or function 'cte‘不可更新，因为修改会影响多个基表。’

我理解抛出错误的原因，但我不明白的是如何修复它。任何建议都将不胜感激！

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;

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)

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)