Menu
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!
710
A Common Table Expression (CTE) is a temporary result set that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.
A DELETE statement can include JOIN operations. It can contain zero, one, or multiple JOIN operations. The DELETE removes records that satisfy the JOIN conditions.
CTE will provide us a temporary result set using which we can delete duplicate records easily from the actual table using a single query.
You can use multiple CTEs in a single query as shown below. You will see that, you join CTEs like you join tables and views.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
