It is possible to UPDATE and SELECT in single statement using OUTPUT clause with CTE containing JOIN?

Question

We have a number of stored procedures that are used by BizTalk to fetch n rows from a queue (and a joined table) and update the status of those items at the same time.

I am trying to modify these queries to remove the use of table variables and instead do the work in a single statement. I have succeeded with some, but this particular example is tricky because there is a join in the CTE, and I want to return some of the joined columns even though they weren't part of the update.

This is what I have come up with:

;with q as
(
    select top (@FetchCount) 
        iq.InQueueId,
        itk.[Message],
        iq.PatNo,
        iq.DistrictNo,
        itk.Interaction,
        iq.[Status]
    from
        InQueue iq
        join Itk on iq.InQueueId = itk.InQueueId
        join [Endpoint] e on iq.[Endpoint] = e.EndpointId
        join EndpointName en on en.EndpointNameId = e.Name
    where
        en.Name = 'XYZ'
        and iq.[Status] = @StatusNew
    order by
        iq.InQueueId
)
update
    q
set
    [Status] = @StatusSelected
output
    inserted.InQueueId as [Id],
    inserted.[Message] as [Msg],
    inserted.DistrictNo,
    inserted.Interaction

This immediately fails with the following error:

The column reference "inserted.Message" is not allowed because it refers to a base table that is not being modified in this statement.

Clearly this is because the columns Message and Interaction cannot be returned as part of the inserted set, because they are in a different table and therefore weren't updated.

So I attempted to change the output clause to:

output
    inserted.InQueueId as [Id],
    q.[Message] as [Msg],
    inserted.DistrictNo,
    q.Interaction

This fails with the error:

The multi-part identifier "q.Message" could not be bound.

Is it possible to achieve this without rewriting the query to use either temporary tables or table variables?

Answer 1

You can use the deleted set in the output clause to reference columns in your CTE in tables that are not updated

e.g.

output
    inserted.InQueueId as [Id],
    deleted.[Message] as [Msg],
    inserted.DistrictNo,
    deleted.Interaction

The deleted and inserted sets in an update could also be thought of as before and after, although the terminology here matches delete...output and insert...output