Post reply

Update the 3 oldest records?

  • November 26, 2007 at 2:34 pm

    #754269

    oops I meant to put this in the 2005 forum

  • November 26, 2007 at 3:10 pm

    #754297

    wrong forum... but here is the answer, it uses some 2005 functionality though with the rownumber and over statements

    I need UPDLOCK and READPAST hints together. As explained here

    Tutorial

    So my statement should read.

    WITH Requests AS

    (

    Select *, ROW_NUMBER() OVER(ORDER BY RequestDate ASC) AS RowNum

    FROM ReportRequest_R2 WITH (UPDLOCK, READPAST)

    WHERE Status = 'O'

    )

    UPDATE Requests

    SET

    Status = 'P',

    StatusDate = getdate(),

    StatusDesc = 'Processing',

    LockGUID = @GUID,

    ProcessCount = ProcessCount + 1

    WHERE RowNum <= 3

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply