Solved

Updating records with NewID() and new row number

Posted on 2004-04-19
7
811 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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

16 Experts available now in Live!

Get 1:1 Help Now