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.

1
50
3/31/2009 12:52:16 PM

Accepted Answer

You want the OUTPUT clause

UPDATE Items SET Clicks = Clicks + 1
OUTPUT INSERTED.Name
WHERE Id = @Id
92
3/31/2009 12:55:18 PM

Accesses table only once :

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon