March 14, 2007 at 12:07 pm
Ok folks, I'm in over my head..
What I'm trying to do is get the next available item from a table which may accessed by multiple people.. only one person should get the next available record.
Currently I'm using something like this:
Update
Pident
Set Status= 1,
@IdentPK = pIdent.Identpk
Where Pident.IdentPK =
(
Select min( po.IdentPK)
From pIdent ID
Join Porder PO on PO.IdentPK = ID.IdentPK
Where id.Status = 0
and PO.status = 0 )
And this seems to work fine.. now what I need to do is escalete items that are due before the current date. Something like this:
Declare
@min-2 numeric(10), @Rush numeric(10)
Select
@min-2 = Isnull(min( po.IdentPK),0)
From pIdent ID
Join Porder PO on PO.IdentPK = ID.IdentPK
Where id.Status = 0
and PO.status = 0
Select
@Rush = isnull(min( po.IdentPK),0)
From pIdent ID
Join Porder PO on PO.IdentPK = ID.IdentPK
Where id.Status = 0
and PO.status = 0
and po.requestshipdate <= getdate()
Select case when @Rush>0 then @Rush else @min-2 end as process
My problem is that I don't see how I can put the latest stuff into a single update statement like before.
If I wrap all this in an explicit transaction rather than a single update statement , would that do the trick? Is there a way of doing it which wouldn't impact performance as much?
Thanks for any suggestions..
March 15, 2007 at 9:46 am
Try something like the following. The UPDLOCKs are important to avoid deadlocks.
UPDATE I
SET Status= 1
,@IdentPK = I.Identpk
FROM pIdent I
JOIN (
SELECT MAX(D1.IdentPK) AS IdentPK -- will ignore NULLs
FROM (
SELECT MIN(I1.IdentPK) AS IdentPK
FROM pIdent I1 WITH (UPDLOCK)
JOIN Porder O1
ON I1.IdentPK = O1.IdentPK
WHERE I1.Status = 0
AND O1.status = 0
AND O1.requestshipdate <= GETDATE()
UNION ALL
SELECT ISNULL(MIN(I2.IdentPK), 0)
FROM pIdent I2 WITH (UPDLOCK)
JOIN Porder O2
ON I2.IdentPK = O2.IdentPK
WHERE I2.Status = 0
AND O2.status = 0
) D1
) D ON I.IdentPK = D.IdentPK
March 15, 2007 at 5:42 pm
This should do it.. and I learned some new stuff looking at it..
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply