C# SQL：更新一行并用 1 个查询返回一个列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/700786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Update a row and returning a column value with 1 query
提问by Robert
I need to update a row in a table, and get a column value from it. I can do this with
我需要更新表中的一行，并从中获取列值。我可以这样做
UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id;
SELECT Name FROM Items WHERE Id = @Id
This generates 2 plans/accesses to the table. Is possibile in T-SQL to modify the UPDATE statement in order to update and return the Name column with 1 plan/access only?
这将生成对表的 2 个计划/访问。是否可以在 T-SQL 中修改 UPDATE 语句，以便仅使用 1 个计划/访问来更新和返回 Name 列？
I'm using C#, ADO.NET ExecuteScalar()or ExecuteReader()methods.
我正在使用 C#、ADO.NETExecuteScalar()或ExecuteReader()方法。
采纳答案by Marc Gravell
回答by Rashack
Use a Stored procedure for this.
为此使用存储过程。
回答by BFree
Create a stored procedure that takes the @id as a parameter and does both of those things. You then use a DbDataAdapterto call the stored procedure.
创建一个将@id 作为参数的存储过程，并执行这两件事。然后使用DbDataAdapter调用存储过程。
回答by Learning
Accesses table only once :
只访问表一次：
UPDATE Items SET Clicks = Clicks + 1 , @Name = Name WHERE Id = @Id;
select @name;
回答by Russ Cam
If you're using SQL Server 2005 onwards, the OUTPUT clauseis ideal for this
如果您使用的是 SQL Server 2005 以后的版本，则OUTPUT 子句非常适合
回答by Kobbe
I could not manage to update and return one row inside a select statement. I.e you can not use the selected value from the other answers.
我无法在 select 语句中更新和返回一行。即您不能使用从其他答案中选择的值。
In my case, I wanted to use the selected value in a query. The solution I came up with was:
就我而言，我想在查询中使用选定的值。我想出的解决方案是：
declare @NextId int
set @NextId = (select Setting from Settings where key = 'NextId')
select @NextId + ROW_NUMBER() over (order by SomeColumnOfYourTable) from YourTable
update Settings set Setting = Setting + @@ROWCOUNT
where key = 'NextId'
