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

c# sql-server tsql ado.net
Robert picture Robert · Mar 31, 2009 · Viewed 68.7k times · Source

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.

Answer

Marc Gravell picture Marc Gravell · Mar 31, 2009

You want the OUTPUT clause

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

Related questions

WHERE IN (array of IDs)

I have webservice which is passed an array of ints. I'd like to do the select statement as follows but keep getting errors. Do I need to change the array to a string? [WebMethod] public MiniEvent[] getAdminEvents(int buildingID, DateTime …

Read More
Use of SqlParameter in SQL LIKE clause not working

I have the following code: const string Sql = @"select distinct [name] from tblCustomers left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');"; using (var command = new …

Read More
How do you deal with transport-level errors in SqlConnection?

Every now and then in a high volume .NET application, you might see this exception when you try to execute a query: System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. According to …

Read More