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

common-table-expression, sql-server-2008-r2, t-sql

Solution

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`

Problem

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?

Original source