thready
asked on
SQL Server - Update order by limit
Hi Experts,
With SQL Server, how does one do something like this?
Update tasks set Status=1 WHERE Status=0 ORDER BY otherColumn LIMIT 1
I've seen this done as a with statement with a select- but this doesn't seem to lock the row - and I need to lock the row...
Thanks,
Mike
With SQL Server, how does one do something like this?
Update tasks set Status=1 WHERE Status=0 ORDER BY otherColumn LIMIT 1
I've seen this done as a with statement with a select- but this doesn't seem to lock the row - and I need to lock the row...
Thanks,
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not familiar with the LIMIT, so I can't help you unless you explain a little further what you're trying to do.
ASKER
I have various machines competing to mark N rows in tasks as reserved (as well as their machine name). I'm trying to avoid collisions because various machines will periodically try to do this... I guess I need an embedded select statement... but if I do it this way- will I get deadlock errors?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it's LockReadOnly... I don't know anything about this, can you explain?
ASKER
I read the docs- I get it- but since it's LockReadonly, I think my problem is with deadlock, not concurrency, right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone