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,
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)
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)).
