Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 875
  • Last Modified:

Updating records with NewID() and new row number

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
brianlees
Asked:
brianlees
  • 4
  • 2
1 Solution
 
a1xCommented:
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
 
LowfatspreadCommented:
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
 
a1xCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
brianleesAuthor Commented:
So I guess there is no way to do it with one statement?
0
 
a1xCommented:
Why do you need to run it in one statement?
0
 
brianleesAuthor Commented:
I would like to do it as a single query call from Cold Fusion.
0
 
a1xCommented:
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

Technology Partners: 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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now