Update random values from a list into a column in MySQL

Subash Chandran 6th April 2020 Leave a Comment

Learn how to update random values from a list into a column in MySQL, Oracle SQL or SQL Server.

Let’s say I want to update date from four different values into a date type column, the SQL would be.

UPDATE `sales`
SET `billdate`=(CASE CEIL(RAND()*4)
              WHEN 1 THEN '2020-03-10'
              WHEN 2 THEN '2020-03-17'
              WHEN 3 THEN '2020-03-25'
              WHEN 4 THEN '2020-03-31'
          END);

The above query would randomly fill values equally.

You can also add a condition so make sure you fill random values based on a condition.

UPDATE store_orders
SET received_date=(CASE order_date
              WHEN '2020-03-10' THEN '2020-03-17'
              WHEN '2020-03-17' THEN '2020-03-25'
              WHEN '2020-03-25' THEN '2020-03-31'
              WHEN '2020-03-31' THEN '2020-04-07'
          END);