Sorry actually you are right. I forgot one important detail.

here is exactly what I do.

first I run this query:

select *

from AGS_Hands_InProcess nolock

where WIn is NULL -- unfinished games

and ts_started<dateadd(hh,-1,getdate()) -- older than 1 hour

Order by ts_started

result1:

handidplayerHandleAGSGameIDUserIDNumberOfBetsTotalBetsWinPJWints_startedts

9200729858253850978580021871259000911129610253C205092323110.0000.00002012-11-18 15:14:03.2772012-11-18 15:14:03.277

then I run this 2nd query:

select top 100 * from UserBalanceAdjustments nolock

where ReasonForAdjustment like 'ADJ AGS%'

and transactiontime>dateadd(ww,-1,getdate()) -- for efficiency, limit the time window, e.g. a week

order by transactiontime desc

result2:

UserIDTransactionTimeAmountBalanceBeforeAdjustmentReasonForAdjustmentOperatorIDTransactionNumberCategoryID

C2050923232012-11-18 16:25:00.49710.0000147.4000ADJ AGSGameID=10253 handid=0 ph=0 txnid=184904079770618

Before I can run the update below. I need to make sure that the userid "C205092323" exist on the result of the second query and the ts_started in the first result "2012-11-18 16:25:00.497" is prior to the transaction time in the second result "2012-11-18 16:25:00.497". Sometimes the transaction doesn't exist in the second result, but it does exist on the first result which mean we don't need to do the update. Another scenario is there are two transaction in the first result for the same userid, but there is only one transaction in the second result. which means we need to compare the transaction time to make sure we update the correct transaction. Another issue is the AGSGameID is different each time, sometimes it is 10253, 10251, or 10252. So these are the reason why we cannot do it all at once.

UPDATE AGS_Hands_InProcess SET

Win= 5, -- the value from the matching Adjustement

ts= getdate() -- the timestamp of last action on this hand

WHERE HandID= 92007

and UserID= 'C205092323'

and AGSGameID= 10253

and Win is NULL -- protection.