Select then update a record in SQL Server 2014

Question

I'd like to select a record in sql and then update it, all in one statement or stored procedre.

So, I have this statement:

select top 1 ID, TimeStamp, Locked, Deleted 
from TableName 
where Locked='False' and Deleted='False' 
order by TimeStamp asc

How can I select that statement and then set Locked='True' to the returning record.

Can you explain why you need to select it? Are you looking to display the record BEFORE changing it or display the record AFTER changing it? — dfundako, Mar 19 '18 at 15:10
I'm looking to select it then lock if immediately afterwards. — Matthew, Mar 19 '18 at 15:12
This is more complex than you would guess. Problem is update does not support top. Please post some test data and desired output. See this https://stackoverflow.com/questions/49338306/sql-threadsafe-update-top-1-for-fifo-queue/49339468#comment85681208_49339468 — paparazzo, Mar 19 '18 at 15:33
Actually you want a prior lock. You want the read to take and update lock so it cannot get changed before YOU change it. — paparazzo, Mar 19 '18 at 16:20

score 3 · Answer 1 · answered Mar 19 '18 at 15:21

3

if you use the output clause, you can see the updated record before it was updated by selecting from deleted

with cte as (
    select  top 1 
            ID, 
            TimeStamp, 
            Locked, 
            Deleted 
    from    TableName 
    where   Locked='False' and Deleted='False' 
    order by TimeStamp asc
)
update  cte 
set     Locked = 'True'
output  deleted.*

answered Mar 19 '18 at 15:21

JamieD77

13,796
1
17
27

There is a (small) window where value(s) could be changed between the read and the write. – paparazzo Mar 21 '18 at 18:19
@paparazzo how so? The CTE doesnt actually get materialized until the update is called. How much time we talking about? It shouldnt be any different that using a derived table. I think you're reading too much into what OP is saying.. I believe when they say `then lock if immediately afterwards` they just mean set Locked=True.. not actually lock the record – JamieD77 Mar 22 '18 at 14:27
Why leave a small window open. See my answer. – paparazzo Mar 22 '18 at 14:36
@paparazzo Its not really a window.. you're updating in a single statement and displaying the output.. what window are you talking about? check out this query `WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X` if the cte is executed before the select, you should get a result – JamieD77 Mar 22 '18 at 14:57
See my first comment. Not going to argue with you. If you don't think I am correct then fine. – paparazzo Mar 22 '18 at 14:59
Oh my.. you're talking about the time it takes between finding the top 1 and updating it.. in the same transaction? have fun worrying about that – JamieD77 Mar 22 '18 at 15:02
The fun is over. See my answer. – paparazzo Mar 22 '18 at 15:03
@paparazzo can you fix your answer so we can actually look at it? – JamieD77 Mar 22 '18 at 15:12

paparazzo · Answer 2 · 2018-03-22T15:14:01.273

If you need it locked then I think this will do it.
Problem is you cannot use top with update.

create table #T (pk int identity primary key, ts timestamp not null, lock bit not null)
insert into #T (lock) values (1), (0), (1), (0), (0);
select * 
from #T t
order by t.ts asc;

update tt 
set tt.lock = 1 
output deleted.*
from ( select top(1) t.* 
       from #T t with (UPDLOCK ROWLOCK) 
       where t.lock = 0 
       --and t.Deleted = 0
       order by t.lock
     ) tt;

select * 
from #T t
order by t.ts asc;

drop table #T

By taking the UPDLOCK the value(s) cannot be changed between the read and the write.

score -1 · Answer 3 · edited Mar 22 '18 at 15:24

-1

Do you need to return the data out or just update it?

If it's just update:

Update Tablename
   Set Locked  = 'True'
 Where Locked  = 'False' 
   and Deleted = 'False'

To give the result out:(add this after the udpate)

select top 1 ID, TimeStamp, Locked, Deleted 
  from TableName 
 where Locked  = 'True' 
   and Deleted = 'False' 
 order by TimeStamp asc

edited Mar 22 '18 at 15:24

Kobi

2,494
15
30

answered Mar 19 '18 at 18:20

vamsi

11
4

Select then update a record in SQL Server 2014

3 Answers3