Home Dashboard Directory Help
Search

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


Status: 

Resolved
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Suggestion
ID: 790237
Opened: 6/14/2013 6:17:39 PM
Access Restriction: Public
4
Workaround(s)
view

Description

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



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

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.

Sincerely,
Manbeen
Sign in to post a workaround.
Posted by PhilipOrleans on 6/16/2013 at 7:42 AM
Dave's code Works.
I have been using this versión to work around the issue:

declare @npa varchar(11)
update s set active=1, @npa=npa
from (select top 1 * from mytable order by newid()) s

where I get the selected data into the variable @npa

The question is, what version is more efficient, Dave's suggestion or the code above.
Posted by Dave Dustin (AucklandSQL) on 6/15/2013 at 8:11 PM
Sorry, I just typed the code without testing. Do you have a unique ID on the table? I mocked this quick (and propbably not optimal) example up.
-- Set up fake environment
CREATE TABLE myTable (
MyID int IDENTITY,
Field varchar(100),
Active bit
) ;

INSERT INTO myTable (Field, Active)
    VALUES ('first', 0),('second', 0),('third', 0),('forth', 0),('fifth', 0),
    ('sixth', 0),('seventh', 0),('eighth', 0),('ninth', 0),('tenth', 0)

-- Storage space for output from update statement
DECLARE @outputtable TABLE (Field varchar(100));

-- Update the table, setting one random row to 1. The 'Field' value is copied into the table variable
UPDATE myTable
SET Active = 1
OUTPUT INSERTED.Field INTO @outputtable
FROM (SELECT TOP 1 MyID FROM myTable t WHERE t.Active=0 ORDER BY NEWID()) AS t
WHERE myTable.MyID = t.MyID ;

-- See the value updated
SELECT * FROM @outputtable ;


Posted by PhilipOrleans on 6/15/2013 at 4:02 AM
The solution proposed by Dave Dustin generates an error. It seems that ORDER BY is not aceptable in that scenario. I am using SQL Server 2012.
Posted by Dave Dustin (AucklandSQL) on 6/15/2013 at 2:33 AM
Use the OUTPUT clause

DECLARE @tempTable TABLE (field varchar(100)) ;

UPDATE TOP (1) mytable
SET Active=1
OUTPUT mytable.field INTO @tempTable
WHERE Active=0
ORDER BY NEWID() ;

The table @variableTable now contains the value from mytable pertaining to the row that was updated.