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.

Arpit Jain · Accepted Answer · 2017-07-11 08:12:19Z

122

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

edited Jul 11, 2017 at 8:12

answered Jan 4, 2016 at 11:09

Arpit Jain

1,2441 gold badge11 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Cory Nelson Over a year ago

I've never even thought of this. Very nice!

Arpit Jain Over a year ago

Thanks Cory, I wondered if the set keyword inside the update will be able to set a variable and it worked!

rahoolm Over a year ago

will this work with insert for a single affected row?

Arpit Jain Over a year ago

No @rahoolm, only the SET and SELECT keywords can modify a variable and these keywords cannot be used with INSERT statement

user1664043 Over a year ago

There are some limits to this approach; for example, if you have a rowversion column, this will return the last rowversion, not the one resulting from your update.

|

Andomar · Accepted Answer · 2013-05-30 23:12:10Z

113

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

answered May 30, 2013 at 23:12

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Norbert Norbertson Over a year ago

You don't have to have a table for the results. You can do this: OUPUT INSERTED.* Of course this will bring back all the cols though.

Carl Nitzsche · Accepted Answer · 2017-07-10 09:23:45Z

12

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

answered Jul 10, 2017 at 9:23

Carl Nitzsche

1311 silver badge4 bronze badges

Collectives™ on Stack Overflow

UPDATE OUTPUT into a variable

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related