update rows during select | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

update rows during select

I know this is probably a newb question, thanks for reading… I want to know how one might go about selecting a bunch of rows while updating a field at the same time? I have a bunch of rows I need to pick out of a table and I need to mark a bit field so next time the procedure runs it won’t select those rows again: select MyID from ThisTable where Picked is null or Picked=0
update ThisTable set Picked=1 where Picked is null or Picked=0 There are lots of records being inserted and updated in this table all the time, and I want to ensure the records I select are the same records I’m updating, so I’d like to combine those two things into one statement of some kind: select MyID, (update ThisTable set picked=1) from ThisTable where Picked is null or Picked=0 I’m not sure if this needs to be done or not… will having two separate statements (one select and one update) ever result in the selected rowset being different than the records being updated? Would it ever be possible for a new record to be inserted after the select but somehow sneak in there before the update statement? Thanks!
No its not possible to select, update in same T-Sql.
you can however set some transaction levels (perhaps serializable) or use more columns in WHERE to restrict the query. ***********************
Dinakar Nethi
SQL Server MVP
You can make use of temp table or table variable….
select MyID into #temp from ThisTable where Picked is null or Picked=0
update t set Picked=1
ThisTable t
join #temp tt on t.myid = tt.myid You will have #temp table after update also if you have to select something else too..