MySQL: update all rows with random numbers from list

Question

I need to run an SQL query that picks a random number from list and updates a specific column with it.

List of numbers:

(101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122)

rand table: (structure)

id  rand    type
1    0       false
2    0       true
3    0       false
4    0       true
5    0       true

I need to pick random number from list and update "rand" column,

Answer 1

Try this:

UPDATE `rand` SET `rand` = (SELECT val FROM (
    SELECT 101 AS val
    UNION ALL SELECT 102
    UNION ALL SELECT 103
    UNION ALL SELECT 104
    UNION ALL SELECT 105
    UNION ALL SELECT 106
    UNION ALL SELECT 107
    UNION ALL SELECT 108
    UNION ALL SELECT 109
    UNION ALL SELECT 110
    UNION ALL SELECT 111
    UNION ALL SELECT 112
    UNION ALL SELECT 113
    UNION ALL SELECT 114
    UNION ALL SELECT 115
    UNION ALL SELECT 116
    UNION ALL SELECT 118
    UNION ALL SELECT 119
    UNION ALL SELECT 120
    UNION ALL SELECT 121
    UNION ALL SELECT 122
) AS vals ORDER BY RAND() LIMIT 1)

It will update the rand column with a random value from the list at each execution

If the list of numbers is from a predefined table you can do this:

UPDATE `rand` SET `rand` = (SELECT `number` FROM `number_table` ORDER BY RAND() LIMIT 1)

Answer 2

If the numbers doesn't come from another table, but you just want any random number between 101 and 122 then I think this should work:

UPDATE `rand` SET `rand` = FLOOR(RAND() * (22)) + 101

-- or written with the formula below.
UPDATE `rand` SET `rand` = FLOOR(101 + RAND() * (123-101))

From the MySQL documentation:

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)).

Sample SQL Fiddle