Update with Priority

Question

Here is the request:

UPDATE  TOP (1) InnerPortal.Feedback.QueueFeedback
SET Busy = 1, BusyBy = @uid
Output inserted.*
WHERE (
        AccountCode = @account and
        (Done is null or Done = 0) and 
        (Busy is NULL or Busy = 0) and 
        ((DATEPART(hour, DATEADD(HOUR, Utc, @utcNow)) >= 9 ) and
        (DATEPART(hour, DATEADD(HOUR, Utc, @utcNow)) <= 20))
)

It gets first founded "non busy" record where the key field is AccountCode.
If record not found request returns nothing.
Next I have an ordered list of AccountCode and the target is to release a special logic:
I need to create something like a loop in this request.
It will get first AccountCode from list and try to update.

if success - it will stop and will return Output inserted.*
If update was not successful request will get second entry from my accounts list and try to update again.

If the loop is over and nothing was updated - Output inserted.* returns nothing (like now).
Is it possible to release in single request? Thanks!

What database and version were you using? Please tag the relevant database version as the answers may vary depending on the database server (and its version) you were using. — Sathish, Apr 29 '15 at 13:46
`•if success - it will stop and will return Output inserted.* ` And loop should stop here or continue? — Deep, Apr 29 '15 at 13:51
Why are you using a loop for an update? Also, you are using top 1 but there is no order by so you have no way of knowing what row will be effected. — Sean Lange, Apr 29 '15 at 13:54
The target is to 'hook' (set Busy = 1) to ANY row where `AccountCode = @account`. Where is many rows with same `AccountCode` and request need to 'hook' anyone of them - top(1) helps. — Alexey Kulikov, Apr 29 '15 at 13:58
@SeanLange `TOP` without `ORDER BY` is non-deterministic by design. Although `UPDATE`, `INSERT`, and `DELETE` support `TOP`, *none* of them support `ORDER BY`. See [Limitations and Restrictions](https://msdn.microsoft.com/en-us/library/ms189463.aspx). [This](http://stackoverflow.com/questions/655010/how-to-update-and-order-by-using-ms-sql) is the common workaround if you need it deterministic. I've only ever used it when I need to limit I/O and each iteration naturally removes records from the set, like moving 10 million records 10,000 at a time. — Bacon Bits, Apr 29 '15 at 14:00
I understand how it works. thanks though for finding an explanation of the way to deal with it. — Sean Lange, Apr 29 '15 at 14:03
@user2598575 I think the problem you are facing is because you are using a loop to do dml. Perhaps you could modify your whole process so it doesn't have to do this RBAR style of manipulating data. — Sean Lange, Apr 29 '15 at 14:04

score 0 · Answer 1 · answered Apr 30 '15 at 07:24

UPDATE TOP (1) InnerPortal.Feedback.QueueFeedback

SET Busy = 1, BusyBy = @uid
Output inserted.* 
from InnerPortal.Feedback.QueueFeedback
join account 
  on AccountCode = account.id
 and AccountCode = @account 
 and (Done is null or Done = 0) 
 and (Busy is NULL or Busy = 0) 
 and (     (DATEPART(hour, DATEADD(HOUR, Utc, @utcNow)) >= 9 ) 
       and (DATEPART(hour, DATEADD(HOUR, Utc, @utcNow)) <= 20) )

Update with Priority

1 Answers1