SQL Server Performance

update rows during select

Discussion in 'SQL Server 2005 General Developer Questions' started by ishmell, Feb 16, 2007.

  1. ishmell New Member

    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!



  2. ndinakar Member

    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
    ***********************
  3. MohammedU New Member

    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..




    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page