title: SQL Server CTE - SELECT after UPDATE using OUTPUT INSERTED
body: <p>I've seen a few posts on using CTE (WITH) that I thought would address my issue but I can't seem to make it work for my specific use case.  My use case is that I have a table with a series of records, and I need to pull some number of records AFTER a small update has been made to them.  </p>
  
  <p>i.e.
  - retrieve records where a series of conditions are met
  - update one or more columns in each of those records
  - return the updated records</p>
  
  <p>I know I can return the IDs of the records using the following:</p>
  
  <pre><code>WITH cte AS 
  ( SELECT TOP 1 * FROM msg 
    WHERE guid = 'abcd' 
    AND active = 1 
    ORDER BY created DESC )
  UPDATE cte SET active = 0 
  OUTPUT INSERTED.msg_id 
  WHERE guid = 'abcd'
  </code></pre>
  
  <p>That nicely returns the msg_id field.  I tried wrapping all of that in a <code>SELECT * FROM msg WHERE msg_id IN ()</code> query, but it fails.</p>
  
  <p>Anyone have a suggestion?  For reference, using SQL Server 2008 R2.</p>