将 OUTPUT 更新为变量 [英] UPDATE OUTPUT into a variable

问题描述

我正在尝试执行更新和选择...基本上，基于索引进行更新，然后选择已更新的行 ID.

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.

使用 OUTPUT 子句很简单:

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

这三个不起作用:

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)

包含最后一个是因为当 Management Studio 中的所有红色波浪线消失时，它让我暂时感到兴奋.唉，我收到这个错误:

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.

推荐答案

如果只有一行受到影响，可以不用表变量.

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 

这篇关于将 OUTPUT 更新为变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！