update top (n) table set @var=field, field1=1 where field1=0 order by newid() - by PhilipOrleans

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 790237 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 6/14/2013 6:17:39 PM
Access Restriction Public


The statement below is imposible to issue in SQL but it Works perfectly in MySQL. It also has a tremendous business sense. It means, give me one record at random and set it "active", all in the same sql statement, which makes it ideal for high-transaction-rate tables.

update top(1) mytable set  @myvariable=field, active=1 where active=0 order by newid()

The statement should sort the records first, take the first one from the sorted intermediate table and flag it as active, and return the business field "field". This is impossible to achieve in one line, or am I wrong? Of course I can do the same in more than one statement, but that means using transactions, since I cannot ever return the same record. Once the record is "active", it may never be used again.

Any help on this??

Sign in to post a comment.
Posted by Microsoft on 7/18/2013 at 1:46 PM

Thank you for submitting this feedback. After carefully evaluating all of the suggestion items in our pipeline, we are closing items that we will not implement in the near future due to current higher priority items. We will re-evaluate the closed suggestions again in the future based on the product roadmap.
Thanks again for providing the product suggestion and continued support for our product.