Updating Random Records 1

[Jenns]

Hi out there,

I have a table with 600 records. I'd like to assign these records randomly to 15 different associates for work. What I need to do is select 40 random records and update the "Owner" column in my table with an associates name. I can't take the TOP 40 records because the table is sorted by the value of the record and it would be unfair to assign the 40 highest quality records to one person.

I belive the RAND function uses integers, however I have no integer values in my table. If I create an identity field in my table which will increment by one can I use the RAND() function? If so, how would I use it?

Thanks!
Jenn

 

[foxdev]

Yes, if you had an Identity column (or any sequential numbered column), you could use RAND() to pick some out. Even if you didn't, you could create a #Temp table that had an Identity column, then INSERT INTO #TEMP SELECT ... FROM REGTABLE, making sure that some unique identifier is being selected from the regular table.

But think a bit more about the SELECT TOP 40 approach. Why not order by something else in the table that has no indication of "quality", such as DateEntered or some such.

 

[Jenns]

Thank you for your insight.

In response to your last paragraph...

Is it possible to do a query selecting the TOP 40 order by, lets say , DateEntered and then updating only those 40. Sort of like two queries in one? I'd rather not create a separate table just to get the sort.

Thanks!
Jenn

 

[Jenns]

After a bunch of unsuccessful queries, I got the results I wanted by writing this query:

update mytable
set owner = 'AssociateName'
where myrec in (select top 40 myrec from mytable WHERE OWNER IS NULL order by myrec)

Myrec is a unique identifer in mytable (which has a character value in this case). I ran the query 15 times. Probably not the most efficient approach, but it worked for me!

Thanks Foxdev!
Thanks!
Jenn