UPDATE OUTPUT to a variable

Question

I am trying to perform an update and select ... basically, an index-based update, and then select the updated row id.

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 in a variable?

 DECLARE @id INT 

These three do not 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) 

This last one turned on because it temporarily turned me on when all the red squigglies went to 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, this can be done without a table variable.

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

Answer 2

Since an update can affect several rows, a table is required for its results:

 declare @ids table (id int); UPDATE Foo SET Bar = 1 OUTPUT INSERTED.Id INTO @ids WHERE Baz = 2 

If you are sure that only one line will be affected, you can pull out the identifier, for example:

 declare @id int select top 1 @id = id from @ids 

Answer 3

Alternatively, if only one line is affected:

 DECLARE @id INT UPDATE Foo SET @id = Bar = 1 ---Yes, this is valid! WHERE Baz = 2 SELECT @id