Update with Output Value
When running an UPDATE statement, you can not only update columns from the table, but you can also update variables. That gives you an interesting opportunity to update a single record, retrieving information about that record at the same time in a single query.
Here's a quick example where you could dequeue a record from a table, update its status, and return the ID of the record that was dequeued, without separate queries:
declare @ID int;
;with first_in_queue as
select top 1 ID from dbo.Records
where Status = 'NEW'
order by CreateDate
set r.Status = 'RUN', @ID = r.ID
from dbo.Records r
join first_in_queue on r.ID = first_in_queue.ID;
select @ID; -- The ID that was dequeued