SQL Server Delete from CTE with join

qcbq4gxm 于 2023-10-15 发布在其他

关注(0)|答案(4)|浏览(159)

I'm trying to implement a FIFO queue using a sql table.

I have the following SQL (modified for posting), the join and param usage are important to the way this process works.

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

running this statement returns an error 'View or function 'cte' is not updatable because the modification affects multiple base tables.'

I understand why the error is thrown, what I can't figure out is how to fix it. Any advice would be much appreciated!

sql-server

来源：https://stackoverflow.com/questions/22761972/delete-from-cte-with-join

4条答案

按热度按时间

l7wslrjt1#

You can use exists() instead of the inner join to MyTable in the CTE.

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;

赞(0）回复(0）举报 2023-10-15

wz3gfoph2#

Something like this?

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)

赞(0）回复(0）举报 2023-10-15

lc8prwob3#

The use of a CTE feels forced here. You can simply:

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)

If you want to use a CTE I like @sarin's answer, but using an EXIST instead:

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)

赞(0）回复(0）举报 2023-10-15

6yt4nkrj4#

I have the same kind of problem, but I would like to remove the rest of the rows, but it doesnt. Any help would make great impact :)

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;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Delete from CTE with join

4条答案

相关问题

热门标签

最新问答