Updating records with NewID() and new row number
Posted on 2004-04-19
I have found how to randomly order a result set from SQL:
ORDER BY NEWID()
Is it possible to also, in one SQL statement, update two additional fields in the table: NewIDNumber and NewRowNumber. Basically, we want to write the NewID() back to the record in another field. We also would like to write the row number of the newly sorted record...so a human can look at it and understand it quickly.
So, the new record would have
Original ID (in this case, a sequential order number), the generated NewID(), and the row number from the random sort listed above.
If you haven't figured it out yet, we are using this to randomly select someone for a drawing. We would like a documentation trail of the selection, which is why we would like to write the NewID() and the row number from the original sort.