Skip to content

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);
See also  java.lang.IllegalStateException: Could not load JDBC driver class [com.mysql.jdbc.Driver] - Spring Boot / Spring Batch error. How to fix?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.