Update table with random values from given set of string values

Question

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

Answer 1

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);

fiddle

Answer 2

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.

Answer 3

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);

Answer 4

Rand() returns the same value for all the rows within a given call. Use newid() to get a random number for every row.

https://web.archive.org/web/20071228004107/http://articles.techrepublic.com.com/5100-10878_11-6089823.html

Update dbo.Employees 
Set City= (Case abs(checksum(NewId()) % 3)
        WHEN 0 THEN 'Lndon'
        WHEN 1 THEN 'New York'
        WHEN 2 THEN 'Paris'
    END
        );