Update Random Rows

To select random records, you can execute:

SELECT TOP 10 *
FROM Products
WHERE ProductName = 'bike'
ORDER BY NEWID()

Now, what I want to do is incorporate the above syntax in an update statement. For example, I want to randomly select 10 rows from a table that meet one condition and then update a column with a value in each of those ten rows. Plus, I want to send to a text file to my C drive with the 10 random rows that were updated. Is this possible?
LVL 3
computerstreberAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TextReportConnect With a Mentor Commented:
I think the example below is what you are after for updating the 10 records.
Cheers, Andrew
UPDATE Products
SET x = 1
FROM Products 
WHERE productid IN (SELECT TOP 10 ProductID
                    FROM Products
                    ORDER BY NEWID()
                   )

Open in new window

0
 
Jinesh KamdarConnect With a Mentor Commented:
I'm not sure if this is allowed in SQL Server, but u can give it a try.

UPDATE
(SELECT TOP 10 ProductName
FROM Products
WHERE ProductName = 'bike'
ORDER BY NEWID()) X
SET X.ProductName = 'BIKE';
0
 
Rick_RickardsConnect With a Mentor Commented:
There's a few issues to consider.  First of all, if you really want to Randomly the first 10 records you will benefit by using the Rnd() function rather than simply sorting by a field name such as NewID().  You should be aware that Rnd() run by itself is not truely random.  It relies on a complex algorithm built by Microsoft to give the appearance of generating random numbers.  To make this pseudo random number even more random you can suply a seed value (I sugest using the primary key).  Run subsequent times it will keep generating different numbers by supplying Rnd() with a seed value each time this will have the effect of reshuffling the deck.

Aside from that you asked for a way to update a field within the set of 10 records.  You can't use the TOP clause in an update query thus you'd normally have to use multiple queries (one to select the top 10 random records and the other to run the update).  This would still leave you with the task of writing out the contents of these 10 records to a file so in truth the task is a lot easier to do in code.

Below is an illustration of how this would be done.  The query uses the TOP clause to select just 10 records, supplies a WHERE clause to illustrate how you would filter the table down by some value and then builds a recordset that is used a few times to complete the process.

First it builds the recordset randomizing the primary key into a much more random number, then it Opens a file for writing, cycles through each record till it's done.  While it visits each record it first updates a field then compiles the entire record into a single string that is then written out to the file previously opened.  Once it's cycled throught to the last record it does a little clean up, closing the file, recordset, database object and the like.

By insuring you have a Reference to the DAO object library and updating the query to fit your situation and the field being updated along with the value you'd like to assign to it before writing out the contents the code below should work as is.

Let me know if you have any questions.


Sub Sample()
    Dim db As dao.Database
    Dim strSQL As String
    Dim rst As dao.Recordset
    Dim fld As Field
    Dim strRecord As String
    
    '*****************************************************************************************************'
    '** Code will randomly select 10 records using the TOP clause within the query as follows.          **'
    '**     "SELECT TOP 10 [tblYourTableName].* " & _
    '** The query selects all records with a PrimaryKeyID Value > 0 using the WHERE clause as follows.. **'
    '**     "WHERE [tblYourTableName].[PrimaryKeyID] > 100 "                                            **'
    '** It Randomly Selects Records by using the Rnd() Function to Randomly generate a number using the **'
    '** PrimaryKeyID field as a seed value and then sorting them in Ascending order as follows..        **'
    '**     "ORDER BY Rnd([tblYourTableName].[PrimaryKeyID]);"                                          **'
    '*****************************************************************************************************'
    
    strSQL = "SELECT TOP 10 [tblYourTableName].* " & _
             "FROM [tblYourTableName] " & _
             "WHERE [tblYourTableName].[PrimaryKeyID] > 100 " & _
             "ORDER BY Rnd([tblYourTableName].[PrimaryKeyID]);"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dao.dbOpenDynaset)
    If rst.RecordCount Then
        Open "C:\Test.txt" For Output As #1  '<<-- Open the file... update this to the path file name as desired '
        Do Until rst.EOF
            rst.Edit
            rst.Fields("Rando").Value = "Some Value"    'Illustrates how to update the field
            rst.Update
            
            For Each fld In rst.Fields
                strRecord = strRecord & "," & fld.Value     '<-- Delimit fields in any way desired.  Here we delimit using a comma ","    '
            Next fld
            strRecord = Mid(strRecord, 2)
            Write #1, strRecord
            strRecord = ""
            rst.MoveNext
        Loop
        Close #1  '<-- Close the file'
        
    End If
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

Open in new window

0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Rick_RickardsConnect With a Mentor Commented:
One correction to Line 5 of my code above.  It's always best to be explicit as to which library is being used, (this avoids conficts with other libraries that have objects by the same name such as ADO) so the line should have read as follows...
    Dim fld As DAO.Field

Open in new window

0
 
Anthony PerkinsConnect With a Mentor Commented:
>>First of all, if you really want to Randomly the first 10 records you will benefit by using the Rnd() function rather than simply sorting by a field name such as NewID().  <<
Suggest you take the time to read up on the NEWID() function for precisely the reason you state "You should be aware that Rnd() run by itself is not truely random."  The result from the NEWID() function is "Truly random"
0
 
Rick_RickardsConnect With a Mentor Commented:
Good point acPerkins.  Just curious though, is there a way to use NewID() within a DAO.Recordset without having to use a pass through query which would render the recordset non-updateable?  It just seemed that given the authors desire to update the 10 random records sent and transfer that data to a file that Access via an ODBC connection offered the simplest solution.

Using NewID() (A Feature of SQL server that renders random GUID's) is there a way to leverage that into an Access/VBA/DAO solution as illustrated above OR is there a good way to do this within SQL server alone that doesn't get overly complex?

0
 
computerstreberAuthor Commented:
Great Answers
0
 
Anthony PerkinsCommented:
>>Just curious though, is there a way to use NewID() within a DAO.Recordset without having to use a pass through query <<
I am afraid I have no idea.

>>It just seemed that given the authors desire to update the 10 random records sent and transfer that data to a file that Access via an ODBC connection offered the simplest solution.<<
DTS can be used for this purpose, without the need for MS Access.
0
All Courses

From novice to tech pro — start learning today.