November 25, 2001 at 11:35 pm
I recently posted a question on the SQL Server Central discussion forum. It was:-
I want to return the identity for a row that was updated using an update statement.
The update statement is as follows:
UPDATE Answers
SET InUse = @SessionID
FROM (Select Top 1 AnswerID from Answers_Eligible) . . .
The update above locks the next AnswerID available and gives it to a data entry operator.
Suggestions were related to storing the AnswerID before the update.
I was already doing this - the problem is that if two requests are made at the same time - then the same AnswerID is returned (due to the time lague between the update and the select).
This is why I wanted to return the AnswerID using the actual update.
Anyone know how to work around these problem related to same-time data requests?
November 26, 2001 at 2:48 am
Didn't catch the first thread, so sorry if my questions go over the same ground, but how are Answers_Eligible and Answers related? You say that if two requests are made at the same time - then the same AnswerID is returned, but the update is on Answers and not Answers_Eligible, so the top 1 is not affected and must return the same row, unless there is a trigger involved?
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 27, 2001 at 10:30 am
If you want to allocate an answer id then you might consider having another table to hold it then
update NextAnswerId
set nextID = nextID + 1, @SessionID = nextID + 1
update answers set
InUse = @SessionID
in this way you don't need to lock any tables except for the duration of the statements.
You can also use the NextAnswerId table for reource locking if you need to.
Cursors never.
DTS - only when needed and never to control.
November 27, 2001 at 10:40 am
I agree with Nigel, but your post is not really clear. What are these two tables? What is the related? How is an identiity related to the update?
Steve Jones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply