将输出更新为变量

我试图执行一个更新和一个选择... 基本上，根据索引更新，然后选择被更新的行 ID。

使用 OUTPUT 子句很简单:

UPDATE Foo
SET Bar = 1
OUTPUT INSERTED.Id
WHERE Baz = 2

但是现在，我怎么把它变成一个变量呢？

DECLARE @id INT

这三个不起作用:

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)

最后一个包括，因为它让我暂时兴奋，当所有的红色波浪形消失在管理工作室。唉，我得到了这个错误:

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.
98816 次浏览
小开

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
小开
最佳答案

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
小开

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

提交答案