3 ms·
mssql has a lot of "tricks" associated with CTE's. Using the OUTPUT clause on updates is one of them.. Here's some queue code using a cte to grab the top one
by buckbova 13y ago
mssql has a lot of "tricks" associated with CTE's. Using the OUTPUT clause on updates is one of them.. Here's some queue code using a cte to grab the top one by queued date.
DECLARE @Queue TABLE (QueueID INT)
WITH q AS (
SELECT TOP (1) QueueID, StatusID, ModifiedDate
FROM worker.Queue WITH (ROWLOCK, READPAST)
WHERE StatusID=(SELECT TOP 1 s.StatusID FROM worker.Status s (NOLOCK) WHERE s.[Status] IN ('Queued'))
ORDER BY QueueDate ASC
)
UPDATE q SET
StatusID=(SELECT s.StatusID FROM worker.Status s (NOLOCK) WHERE s.[Status]='Processing'),
StatusMessage='New Process',
ModifiedDate=GETDATE()
OUTPUT INSERTED.QueueID INTO @Queue
- pradocchia 13y agoThe OUTPUT clause in MSSQL doesn't require a CTE, and can be used with INSERT, DELETE and MERGE in addition to UPDATE. Likewise, your example can also be done with derived tables, which predate CTEs in MSSQL: UPDATE a SET col2 = 10 OUTPUT INSERTED.key1 INTO @local_table (key1) FROM (SELECT TOP (1) * FROM table1 ORDER BY col1) a
- ddebernardy 13y agoLooks like Postgres' RETURNING clause... The latter works with inserts and updates, too, e.g.: with foo as ( insert ... values ... returning * ), bar as ( insert ... select ... from foo returning * ) select ... from foo, bar, baz ...