Solved

Updating records with NewID() and new row number

Posted on 2004-04-19
7
845 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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