UPDATE OUTPUT into a variable

Question

I'm trying to perform an update and a select ... basically, update based on an index, and then select the row id that was updated.

This is simple using the OUTPUT clause:

UPDATE Foo SET Bar = 1 OUTPUT INSERTED.Id WHERE Baz = 2 

But now, how do I get this into a variable?

DECLARE @id INT 

These three don't work:

UPDATE Foo SET Bar = 1 OUTPUT @id = INSERTED.Id WHERE Baz = 2  SET @id = (UPDATE Foo  SET Bar = 1  OUTPUT INSERTED.Id  WHERE Baz = 2)  SET @id = (SELECT Id FROM (UPDATE Foo                  SET Bar = 1                  OUTPUT INSERTED.Id Id                  WHERE Baz = 2) z) 

That last one included because it had me temporarily excited when all the red squigglies went away in Management Studio. Alas, I get this error:

A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement. 

Answer 1

If only one row is affected, it can be done without a table variable.

DECLARE @id INT  UPDATE Foo  SET Bar = 1, @id = id  WHERE Baz = 2  SELECT @id