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

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

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

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

Collectives™ on Stack Overflow

UPDATE OUTPUT into a variable

3 Answers 3

7 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Linked

Related