Link to home
Start Free TrialLog in
Avatar of thready
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
ASKER CERTIFIED SOLUTION
Avatar of Russ Suter
Russ Suter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of thready
thready

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thready

ASKER

it's LockReadOnly...  I don't know anything about this, can you explain?
Avatar of thready

ASKER

I read the docs- I get it- but since it's LockReadonly, I think my problem is with deadlock, not concurrency, right?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thready

ASKER

Thanks everyone