SQL: update row and return column value with 1 query

Question

I need to update a row in a table and get the column value from it. I can do it with

UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id; SELECT Name FROM Items WHERE Id = @Id 

This generates 2 plans / table calls. Is it possible to modify the UPDATE statement in T-SQL to update and return the Name column with only 1 plan / access?

I am using C #, ADO.NET ExecuteScalar() or ExecuteReader() .

Answer 1

Do you want OUTPUT

 UPDATE Items SET Clicks = Clicks + 1 OUTPUT INSERTED.Name WHERE Id = @Id 

Answer 2

Access to the table only once:

 UPDATE Items SET Clicks = Clicks + 1 , @Name = Name WHERE Id = @Id; select @name; 

Answer 3

If you are using SQL Server 2005 onwards, the OUTPUT clause is perfect for this.

Answer 4

Use a stored procedure for this.

Answer 5

Create a stored procedure that takes the @id value as a parameter and does both of these things. Then you can use the DbDataAdapter to call the stored procedure.

Answer 6

I was unable to update and return a single line inside the select statement . Ie you cannot use the selected value from other answers.

In my case, I wanted to use the selected value in the 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'