I want update my table with random values from given set, not from another table.
e.g. value1, value2, value3
and MySQL query should be update all records from above values.
I am looking similar type of solution but with random string values from given set: Update column with random value
I would use the elt() function:
update tablename
set columnname = elt(floor(rand()*3) + 1, 'value1', 'value2', 'value3');
elt() chooses a particular element from a list. rand() produces a random values.
case, when, then for 18k values!
Use floor(rand()*3) to generate a random number among 0, 1, and 2, then use case when to assign value
update test
set i = (case floor(rand()*3)
when 0 then 0
when 1 then 10
when 2 then 20
end);
Try with CEIL and RAND function
UPDATE `table`
SET `column`=(CASE CEIL(RAND()*3)
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END);
Rand() returns the same value for all the rows within a given call. Use newid() to get a random number for every row.
Update dbo.Employees
Set City= (Case abs(checksum(NewId()) % 3)
WHEN 0 THEN 'Lndon'
WHEN 1 THEN 'New York'
WHEN 2 THEN 'Paris'
END
);
