Lynn Pettis (3/3/2013)
Curious if the following would work in this situation with proper indexing of the target table.
WITH TableA_CTE AS(
SELECT TOP (@TOP_HANDLED_ROWS)
TableAID,
TableAStatusTypeID,
LastUpdateTime
FROM
dbo.TableA
WHERE
WaitingRoomCreationTime < dateadd(minute, -@wrExpirationMinutes, @curUtcDate)
AND TableAStatusTypeID IN (1,2,3,4) -- CreatePending/Creating/Created/CreatedReportFailed
ORDER BY
WaitingRoomCreationTime asc
)
UPDATE TableA_CTE SET
TableAStatusTypeID= 9,
LastUpdateTime = @curUtcDate;
go
More than likely... if you really needed the TOP functionality after you did the proper indexing, which I would think you wouldn't.
--Jeff Moden
Change is inevitable... Change for the better is not.