SELECT a cell and UPDATE at the same time?

Question

I've got a SQL Server table in which I have a column where I would like to select the current value and increment by one, is there a way to do this in a single query? This in order to mitigate the chance, however small it might be, that someone else gets the same number.

Something along the lines of this pseudo code:

SELECT NumSeriesCurrent 
FROM NumSeries 
(UPDATE NumSeries SET NumSeriesCurrent = NumSeriesCurrent+1) 
WHERE NumSeriesKey='X'

`UPDATE NumSeries SET NumSeriesCurrent += 1 WHERE NumSeriesKey='X'`? Or you don't mean that? — Martin Smith, May 31 '13 at 11:57
@MartinSmith Well, that takes care of the updating, but I still won't get the value in NumSeriesCurrent previous to the update? — JaggenSWE, May 31 '13 at 11:58
@EASI - To simulate the `OUTPUT` clause in MySQL you need to use a trigger that returns a result AFAIK. — Martin Smith, May 31 '13 at 12:04
possible duplicate of [Is there a way to SELECT and UPDATE rows at the same time?](http://stackoverflow.com/questions/497464/is-there-a-way-to-select-and-update-rows-at-the-same-time) — Thomas Mueller, May 31 '13 at 15:59

score 5 · Accepted Answer · answered May 31 '13 at 11:59

5

To update the value and get the value in NumSeriesCurrent previous to the update you can use

UPDATE NumSeries 
SET NumSeriesCurrent += 1
OUTPUT DELETED.NumSeriesCurrent
WHERE NumSeriesKey='X'

answered May 31 '13 at 11:59

Martin Smith

438,706
87
741
845

Didn't know about the *OUTPUT* part... +1 – Robert Koritnik May 31 '13 at 12:08

SELECT a cell and UPDATE at the same time?

1 Answers1