[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Easy urgent 500 pts question select+update

Posted on 2005-05-02
7
Medium Priority
?
291 Views
Last Modified: 2010-03-19
Hi to all...

I would like to select a row from a table and apdate a LockID field in the same row....

set @PartnerID=0
select @PartnerID=PartnerID from tblPartners where PartnerState=1
if @PartnerID<>0
begin
    update tblPartners set LockID=1 where PartnerID=@PartnerID
end

my main problem is, that another sp may change the PartnerState of the row AFTER the select and BEFORE the update...

is there any way to make an update and the select TOGETHER???


0
Comment
Question by:cosie
7 Comments
 
LVL 13

Expert Comment

by:softplus
ID: 13907644
UPDATE tblPartners SET LockId=1 WHERE PartnerState=1 AND PartnerId <> 0
should do the trick :) (possibly add "AND LockId <> 1" to the end, if you want to make sure it's not locked yet)

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13907660
BOL says you can use a cursor with 'FOR UPDATE'. This may be what you're after. (I've never done it before though)
0
 
LVL 9

Expert Comment

by:noulouk
ID: 13907684
Something like this:
update tblPartners set LockID=1 where PartnerID IN (select PartnerID from tblPartners where PartnerState=1 AND PartnerID<>0)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cosie
ID: 13907690
softplus: :) I need some data into local variables from the updated row, eg. I need @PartnerID to filled ot to know lated in the sp if the update was succesfull or not...
0
 
LVL 14

Accepted Solution

by:
Jan Franek earned 2000 total points
ID: 13907704
You probably need to lock table tblPartners to ensure, that there's no update from another transaction between your select and update. You can do it by seeting proper transaction isolation level (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
set @PartnerID=0
select @PartnerID=PartnerID from tblPartners where PartnerState=1
if @PartnerID<>0
begin
    update tblPartners set LockID=1 where PartnerID=@PartnerID
end
COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This way nobody can change PartnerState of your PartnerId until your transaction commits.
0
 

Author Comment

by:cosie
ID: 13907709
hi noulouk,

this seems to wit my needs, the question is:
Does the sql server allow other sp-s to modify the row between the select and the update???
I'll test it soon...

thx
0
 

Author Comment

by:cosie
ID: 13907719
Hi Franek,
However this locks the whole table, not only the row beeing updated , my table is very short so this solution fits my needs, excellent!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question