UPDATE OUTPUT into a variable

Solution 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 

Solution 2:

Because an update can affect multiple rows, it requires a table to store its results:

declare @ids table (id int);

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

If you're sure only one row will be affected, you can pull out the id like:

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

Solution 3:

Alternatively, If only one row is being affected:

DECLARE @id INT

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

SELECT @id