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.
3 Answers
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
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
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
