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