SQL Update Question...

[skeamy]

I have got a table of TID's which have an available flag<br>What I want to do is take off the next available TID and mark it as unavailable. Ive done the SQL for that<br><br>UPDATE TIDS <br>SET Available=0<br>WHERE ID IN (SELECT TOP 1 ID FROM TIDS WHERE Available=1 ORDER BY TID)<br><br>The only problem is I have no idea which TID it has updated. Is there some way of telling - like @@IDENTITY when you do an INSERT. <br>I would like to do it all in one SQL statement because this procedure is going to take some hammering and I don't want to risk two instances conflicting with each other.<br><br>Sorry Im in a rush..but you get the jist of the problem.<br><br>Thanks in advance.<br>

 

[Invicta]

An easy solution would be to add an attribute, say &quot;marker&quot; (bit).&nbsp;&nbsp;You then modify your UPDATE statement to set the marker to 1 as well.&nbsp;&nbsp;When you've finished whatever you need the marker for, set it back to 0, ready for the next time.<br><br>UPDATE TIDS <br>SET Available=0, Marker = 1<br>WHERE ID IN (SELECT TOP 1 ID FROM TIDS WHERE Available=1 ORDER BY TID)

 

[skeamy]

Thanks for the reply but I don't really see how that makes it tighter. Another instance will have the same code so could nip in and set another marker before my SQL statement has interoggated which TID the first instance set the marker on.<br>Or am I too much of a perfectionist?

 

[Invicta]

You're right.&nbsp;&nbsp;I did say it was an easy solution!<br><br>You need a more sophisticated approach if what you describe is possible.&nbsp;&nbsp;Do you need to know which records were affected every time this update is run or do you just need to know periodically?&nbsp;&nbsp;What will you do with the affected records?<br>