Tutorial :SQL: Update a row and returning a column value with 1 query



Question:

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.


Solution:1

You want the OUTPUT clause

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


Solution:2

Accesses table only once :

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


Solution:3

If you're using SQL Server 2005 onwards, the OUTPUT clause is ideal for this


Solution:4

Use a Stored procedure for this.


Solution:5

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

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'  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »