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.

score 110 · Accepted Answer · answered Mar 31 '09 at 12:55

110

You want the OUTPUT clause

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

answered Mar 31 '09 at 12:55

Marc Gravell

1,026,079
266
2,566
2,900

And above, yes. With SQL Server 2000, see Learning's response – Marc Gravell Mar 31 '09 at 13:07
1

IIRC, the OUTPUT clause was introduced as part of SQL Server 2005 Service Broker - http://msdn.microsoft.com/en-us/library/ms345108.aspx – Russ Cam Mar 31 '09 at 13:11
1

Thanks, how can I retrieve the output parameter from ExecuteNonQuery? – Robert Mar 31 '09 at 14:03
The question cited ExecuteReader ;-p For ExecuteNonQuery, and assuming there is only 1 row, then maybe the approach Learning mentions. – Marc Gravell Mar 31 '09 at 14:16
1

OUTPUT gets upset with triggers. Very expansive about balking on it. – micahhoover Mar 22 '16 at 16:03

score 24 · Answer 2 · edited Jul 16 '20 at 08:08

24

Accesses table only once :

DECLARE @Name varchar(MAX);

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

edited Jul 16 '20 at 08:08

Serg

2,346
3
29
38

answered Mar 31 '09 at 12:55

Learning

8,029
3
35
46

Marc's approach is much better because my query inherently assumes that there is one name for one Id which might/not be true. – Learning Mar 31 '09 at 13:11
2

This approach does not require using a table variable. – crokusek Aug 09 '12 at 23:18
1

This approach is way better, because it does not depend on table triggers. – Mateusz Myślak Jan 05 '18 at 06:48
For a single result, this is very clever. Thank you. – PRMan Jul 15 '20 at 18:47
Great way for using with TOP (1) and get affected row ID (PK) – Ercument Eskar Dec 27 '21 at 13:16

score 4 · Answer 3 · answered Mar 31 '09 at 12:56

4

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

answered Mar 31 '09 at 12:56

Russ Cam

124,184
33
204
266

score 0 · Answer 4 · answered Oct 31 '17 at 14:04

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'

score 0 · Answer 5 · answered Mar 31 '09 at 12:54

0

Use a Stored procedure for this.

answered Mar 31 '09 at 12:54

Rashack

4,667
2
26
35

score 0 · Answer 6 · answered Mar 31 '09 at 12:54

0

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.

answered Mar 31 '09 at 12:54

BFree

102,548
21
159
201

score 0 · Answer 7 · answered Jun 01 '23 at 16:07

I needed this in sqlite.

In sqlite you can do this by using the Returning clause.

UPDATE Items SET Clicks = Clicks + 1
WHERE Id = @Id
Returning Name

For more info see: https://www.sqlite.org/lang_returning.html

You can also return all fields like this:

UPDATE Items SET Clicks = Clicks + 1
WHERE Id = @Id
Returning *

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

7 Answers7

Linked

Related