UPDATE OUTPUT into a variable

Sql Server

Sql Server Problem Overview


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.

Sql Server Solutions


Solution 1 - Sql Server

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 - Sql Server

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 - Sql Server

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 

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionCory NelsonView Question on Stackoverflow
Solution 1 - Sql ServerArpit JainView Answer on Stackoverflow
Solution 2 - Sql ServerAndomarView Answer on Stackoverflow
Solution 3 - Sql ServerCarl NitzscheView Answer on Stackoverflow