[SOLVED] Delete from CTE with join

Delete from CTE with join

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!

Solution

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;