Updating random records of a table using Rand().

We have seen how to get random records from a table by using RAND() function. We can use RAND() function to update records randomly. If we use only RAND() function then our query will update all the records which will not serve our purpose.

For example we have 50 records in our table and we want to randomly update 2 records by changing the status field to 'F'.
update `plus2net_tutorial`  set status='F' ORDER BY RAND() limit 2
Above query will randomly select two records to change the status to 'F'. Here if the status is already set to 'F' then that record will not be updated. To get the total number of records updated we can use mysql_affected_rows() function.

If we want to update 2 records irrespective of its previous value then it is better to first change the status to 'T' by this query.
update `plus2net_tutorial`  set status='T'
This will change all record status to 'T'. After this now let us apply the query
update `plus2net_tutorial`  set status='F' ORDER BY RAND() limit 2
This will update 2 records of the table and change the status from T to F.

Example of Use

This query is useful when we want to show random records for a period ( say photo galleries of this week ) . Here we can't directly select random records and display as each user will see different results. So we will use these queries to update 5 records and then display these five to the users.


* indicates required
Subscribe to plus2net


    Post your comments , suggestion , error , requirements etc here .

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer