SQL random value in UPDATE

In MYSQL db, I need to UPDATE table "people" with a rando开发者_如何学Gom number between 8 and 120, but if the value is between 103 and 109, I want it to become 110.

How would I do such a query?

UPDATE people SET column1 = '________random expression_________'

---

I haven't tested, but perhaps it should work.

UPDATE people 
SET column1 = (
    SELECT if(r.rand BETWEEN 103 AND 109, 110, r.rand)
    FROM ( SELECT floor(8+rand()*113) rand ) r
)

---

I wrote a Perl script to run a SQL query 20k times, and the output suggests that the SELECTed value is correct (clearly, you just want to lift and tweak the SQL from the script to update your table, but I include the script so you can prove it does work):

#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;

my $dbh = DBI->connect( "dbi:mysql:test", "root", "" ) or die $!;
my $q = <<EOQ;
select if(r.bar between 103 and 109, 110, r.bar )
 from ( select floor( rand() * 113 ) + 8 as bar ) r
EOQ
;
my $sth = $dbh->prepare( $q );
my $vals = {};
for ( 0 .. 20000 ) {
  $sth->execute();
  my $row = $sth->fetchrow_arrayref();
  my $int = $row->[0];
  $vals->{$int}++;
}
print join( "\n", sort keys %$vals ), "\n";

---

Guess you could always just do

UPDATE people SET column1 =  floor(8+rand()*113)

UPDATE people 
SET column1 =  110
WHERE column1 BETWEEN 103 AND 109

Or another way which appears to work but am sure will be bettered.

UPDATE people 
SET column1 = 
(
SELECT rand FROM
(
SELECT 8 as rand UNION ALL
SELECT 9 as rand UNION ALL
SELECT 10 as rand UNION ALL
SELECT 11 as rand UNION ALL
SELECT 12 as rand UNION ALL
SELECT 13 as rand UNION ALL
SELECT 14 as rand UNION ALL
SELECT 15 as rand UNION ALL
SELECT 16 as rand UNION ALL
SELECT 17 as rand UNION ALL
SELECT 18 as rand UNION ALL
SELECT 19 as rand UNION ALL
SELECT 20 as rand UNION ALL
SELECT 21 as rand UNION ALL
SELECT 22 as rand UNION ALL
SELECT 23 as rand UNION ALL
SELECT 24 as rand UNION ALL
SELECT 25 as rand UNION ALL
SELECT 26 as rand UNION ALL
SELECT 27 as rand UNION ALL
SELECT 28 as rand UNION ALL
SELECT 29 as rand UNION ALL
SELECT 30 as rand UNION ALL
SELECT 31 as rand UNION ALL
SELECT 32 as rand UNION ALL
SELECT 33 as rand UNION ALL
SELECT 34 as rand UNION ALL
SELECT 35 as rand UNION ALL
SELECT 36 as rand UNION ALL
SELECT 37 as rand UNION ALL
SELECT 38 as rand UNION ALL
SELECT 39 as rand UNION ALL
SELECT 40 as rand UNION ALL
SELECT 41 as rand UNION ALL
SELECT 42 as rand UNION ALL
SELECT 43 as rand UNION ALL
SELECT 44 as rand UNION ALL
SELECT 45 as rand UNION ALL
SELECT 46 as rand UNION ALL
SELECT 47 as rand UNION ALL
SELECT 48 as rand UNION ALL
SELECT 49 as rand UNION ALL
SELECT 50 as rand UNION ALL
SELECT 51 as rand UNION ALL
SELECT 52 as rand UNION ALL
SELECT 53 as rand UNION ALL
SELECT 54 as rand UNION ALL
SELECT 55 as rand UNION ALL
SELECT 56 as rand UNION ALL
SELECT 57 as rand UNION ALL
SELECT 58 as rand UNION ALL
SELECT 59 as rand UNION ALL
SELECT 60 as rand UNION ALL
SELECT 61 as rand UNION ALL
SELECT 62 as rand UNION ALL
SELECT 63 as rand UNION ALL
SELECT 64 as rand UNION ALL
SELECT 65 as rand UNION ALL
SELECT 66 as rand UNION ALL
SELECT 67 as rand UNION ALL
SELECT 68 as rand UNION ALL
SELECT 69 as rand UNION ALL
SELECT 70 as rand UNION ALL
SELECT 71 as rand UNION ALL
SELECT 72 as rand UNION ALL
SELECT 73 as rand UNION ALL
SELECT 74 as rand UNION ALL
SELECT 75 as rand UNION ALL
SELECT 76 as rand UNION ALL
SELECT 77 as rand UNION ALL
SELECT 78 as rand UNION ALL
SELECT 79 as rand UNION ALL
SELECT 80 as rand UNION ALL
SELECT 81 as rand UNION ALL
SELECT 82 as rand UNION ALL
SELECT 83 as rand UNION ALL
SELECT 84 as rand UNION ALL
SELECT 85 as rand UNION ALL
SELECT 86 as rand UNION ALL
SELECT 87 as rand UNION ALL
SELECT 88 as rand UNION ALL
SELECT 89 as rand UNION ALL
SELECT 90 as rand UNION ALL
SELECT 91 as rand UNION ALL
SELECT 92 as rand UNION ALL
SELECT 93 as rand UNION ALL
SELECT 94 as rand UNION ALL
SELECT 95 as rand UNION ALL
SELECT 96 as rand UNION ALL
SELECT 97 as rand UNION ALL
SELECT 98 as rand UNION ALL
SELECT 99 as rand UNION ALL
SELECT 100 as rand UNION ALL
SELECT 101 as rand UNION ALL
SELECT 102 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 111 as rand UNION ALL
SELECT 112 as rand UNION ALL
SELECT 113 as rand UNION ALL
SELECT 114 as rand UNION ALL
SELECT 115 as rand UNION ALL
SELECT 116 as rand UNION ALL
SELECT 117 as rand UNION ALL
SELECT 118 as rand UNION ALL
SELECT 119 as rand UNION ALL
SELECT 120 as rand

) T
ORDER BY rand()
LIMIT 1
)

---

update people 
   set `column1` = if ((@a := floor(8+rand()*113)) BETWEEN 103 AND 109 , 110, @a)
       ;

---

Generate the random number in your code, and pass it to the database. Databases are designed for working with data and are not the ideal place to do this.