Update random values from a list into a column in MySQL
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);