How to update multiple random entries in one column at once in mysql?

Question

I wish to update the values in column stemp but all must be different and random between 30 and 70.

for($i=0;$i<=30;$i++)
{
    $temp= mt_rand(30,70);
    mysqli_query($con,"UPDATE sensor SET stemp= $temp");
}

http://stackoverflow.com/questions/14798640/creating-a-random-number-using-mysql — Disha V., Jul 21 '15 at 13:32
BTW: You know, that you always update all entries, since you don't have a WHERE clause? — Rizier123, Jul 21 '15 at 13:42

score 2 · Accepted Answer · answered Jul 21 '15 at 13:36

2

You can also use MySQL's RAND() function:

mysqli_query($con, "UPDATE sensor SET stemp=ROUND(RAND() * 40) + 30");

Putting this into a loop does not make sense either since you don't seem to have any kind of WHERE condition in your update statement so all records will be updated every time.

answered Jul 21 '15 at 13:36

martynasma

8,542
2
28
45

what can be done to keep on updating after every 10 seconds – bhishaj Jul 21 '15 at 15:40
Maybe run a cron job that executes a PHP script every 10 seconds? http://stackoverflow.com/questions/2689284/executing-php-with-crontab – martynasma Jul 21 '15 at 16:09

score 1 · Answer 2 · answered Jul 21 '15 at 13:32

I think you want to change your logic a bit:

First of create an array from 30 - 70 with range(). Then shuffle() the array and take an array_slice() from it. With this you have 30 elements, which you can loop through an update your db entries. Like this:

<?php

    $arr = range(30, 70);
    shuffle($arr);
    $update = array_slice($arr, 0, 30);

    foreach($update as $v)
        mysqli_query($con, "UPDATE sensor SET stemp = $v");

?>

How to update multiple random entries in one column at once in mysql?

2 Answers2