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...

Who is Participating?
Russ SuterCommented:
The ORDER BY clause is not valid with an update statement. What exactly are you trying to accomplish?
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.
threadyAuthor Commented:
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Russ SuterCommented:
I'd be more worried about concurrency errors than deadlock errors. What is your record locking model? Optimistic, pessimistic, other?
threadyAuthor Commented:
it's LockReadOnly...  I don't know anything about this, can you explain?
threadyAuthor Commented:
I read the docs- I get it- but since it's LockReadonly, I think my problem is with deadlock, not concurrency, right?
Daniel WilsonCommented:
That syntax is really close to what MySQL supports, but MS SQL Server doesn't.  If your WITH clause gets it for you, good.

Otherwise you may need a stored procedure.

create procedure SetStatus
 declare @ID int
 Set @ID = (Select top 1 ID from tasks where Status = 0 ORDER BY otherColumn)
 Update tasks set Status=1 WHERE ID=@ID

Open in new window

The procedure makes it a transaction which, I think, will take care of your concurrency issues.
threadyAuthor Commented:
Thanks everyone
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.