?
Solved

Updating records with NewID() and new row number

Posted on 2004-04-19
7
Medium Priority
?
858 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 375 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 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