Updating two records - Need to Lock database! 1

[hinchdog]

I have a table in SQL Server that contains a column to track the current administrator. This is set to True for only ONE given record at any second. Also, one record MUST by set to True at any given time. Here's my dilemma - I want to be able set the current column to False for one record and True to another. If I were to perform two UPDATES using ADO, there might be a millisecond where no records were True during the switchover causing possible problems. Is there a way to lock the database from being accessed while this operation takes place, then unlock it afterward. Please help! Thanks

-Hinch

 

[Qaroven]

Is it THAT important - ie not even for a millisecond?

Suppose having two flagged for that millisecond is also no good?

Look in the SQLBOL index under HINTS for statement and table/record locking Ben
+61 403 395 052

 

[palbano]

It sounds like you are describing the principles of a TRANSACTION

"But, that's just my opinion... I could be wrong".
-pete

 

[RobV]

You could also use some nice boolean logic to update your tables in a single update statement:

update tblAdministrators 
set IsAdmin = (case when AdminID = <newid> then 1 else 0 end) 

where AdminID = <oldid> or AdminID = <newid>

You could leave out the where clause to make sure that all other records are false as well (or in case you don't know the old admin id). Yours,

Rob.