CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
joelc
joelc

Reputation: 2761

SQL Server CTE - SELECT after UPDATE using OUTPUT INSERTED

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.

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

I know I can return the IDs of the records using the following:

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'

That nicely returns the msg_id field. I tried wrapping all of that in a SELECT * FROM msg WHERE msg_id IN () query, but it fails.

Anyone have a suggestion? For reference, using SQL Server 2008 R2.

Upvotes: 1

Views: 4604

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

    CREATE TABLE #t (msg_id int)
   ; 
    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 INTO #t
    WHERE guid = 'abcd'

    SELECT *
    FROM #t

Upvotes: 3

usr
usr

Reputation: 171226

You can select the data that you need by just adding all columns that you want. INSERTED contains all columns, not just the ones written to. You can also output columns from the cte alias. Example:

OUTPUT INSERTED.SomeOtherColumn, cte.SomeOtherColumn

Upvotes: 2

Related Questions