Solved

Updating records with NewID() and new row number

Posted on 2004-04-19
7
816 Views
Last Modified: 2012-06-22
I have found how to randomly order a result set from SQL:

SELECT *
FROM TABLE1
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.

Brian
0
Comment
Question by:brianlees
  • 4
  • 2
7 Comments
 
LVL 5

Expert Comment

by:a1x
ID: 10860503
I wish I could find a way to update row number per your request.  I've needed it!

Here is how I've re-sorted records:

-- Create the new id for the sort, reset the rownumber to null
UPDATE TABLE1 SET NewIDNumber = NewID(), NewRowNumber = NULL

DECLARE @RowNum int
SET @RowNum = 1

-- Update the RowNumber one record at a time.
UPDATE TABLE1 SET NewRowNumber = @RowNum WHERE ID = (SELECT TOP 1 ID FROM TABLE1 WHERE NewRowNumber IS NULL ORDER BY NewIDNumber)
WHILE @@RowCount > 0 -- Will be 0 when all records are updated
      BEGIN
      SET @RowNum = @RowNum + 1
      UPDATE TABLE1 SET NewRowNumber = @RowNum WHERE ID = (SELECT TOP 1 ID FROM TABLE1 WHERE NewRowNumber IS NULL ORDER BY NewIDNumber)
      END

NewRowNumber and NewIDNumber should be indexed for this to run reasonably fast.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 10861623
update Yourtable
set NewIDNumber=NewID

Select A.OriginalID
         ,Count(*) as X
   into #temp
 From YourTable as A
 Inner Join YourTable as B
  on a.newidnumber <= b.newidnumber
  where a.originalid <= b.originalid

update YourTable
  set NewRowNumber = X
 From YourTable as A
 inner Join #Temp as B
 on A.originalid = b.originalID

3 statements.... all done with set processing  
no need for a programmed loop...

hth

 
0
 
LVL 5

Expert Comment

by:a1x
ID: 10861774
Very nice hth. This solves a problem I've had for a long time!  

I think that "a.originalid <= b.originalid" should be eliminated?

You should be able to combine the second two statements into one, and eliminate the temp table.

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:brianlees
ID: 10861785
So I guess there is no way to do it with one statement?
0
 
LVL 5

Expert Comment

by:a1x
ID: 10861866
Why do you need to run it in one statement?
0
 

Author Comment

by:brianlees
ID: 10861874
I would like to do it as a single query call from Cold Fusion.
0
 
LVL 5

Expert Comment

by:a1x
ID: 10862110
I don't know anything about Cold Fusion.  If it uses ODBC, you can run a passthrough query, which allows you to run multiple commands in one string.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now