Search code examples
sqlsql-servert-sqlcommon-table-expressiondml

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;