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 
