Updating records Randomly of table using RAND() function

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.

This article is written by plus2net.com team.

plus2net.com

▼ More on getting records from table with different combinations of commands