SQL: Update a row and returning a column value with 1 query
C#Sql ServerTsqlado.netC# Problem Overview
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?
I'm using C#, ADO.NET ExecuteScalar() or ExecuteReader() methods.
C# Solutions
Solution 1 - C#
You want the OUTPUT clause
UPDATE Items SET Clicks = Clicks + 1
OUTPUT INSERTED.Name
WHERE Id = @Id
Solution 2 - C#
Accesses table only once :
DECLARE @Name varchar(MAX);
UPDATE Items SET Clicks = Clicks + 1 , @Name = Name WHERE Id = @Id;
SELECT @Name;
Solution 3 - C#
If you're using SQL Server 2005 onwards, the OUTPUT clause is ideal for this
Solution 4 - C#
Use a Stored procedure for this.
Solution 5 - C#
Create a stored procedure that takes the @id as a parameter and does both of those things. You then use a DbDataAdapter to call the stored procedure.
Solution 6 - C#
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.
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'