MySQL update with random number between 1 - 3

The syntax for updating a column with random number between 1-3 is is as follows −

update yourTableName set yourColumnName=FLOOR(1+RAND()*3);

To understand the above syntax, let us first create a table. The query to create a table is as follows −

mysql> create table UpdateNumber1To3
-> (
-> MyNumber int
-> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into UpdateNumber1To3 values(100);
Query OK, 1 row affected (0.16 sec)

mysql> insert into UpdateNumber1To3 values(140);
Query OK, 1 row affected (0.25 sec)

mysql> insert into UpdateNumber1To3 values(130);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from UpdateNumber1To3;

The following is the output −

+----------+
| MyNumber |
+----------+
| 100      |
| 140      |
| 130      |
+--------+
3 rows in set (0.00 sec)

Here is the query to update the MyNumber column values from 1 to 3 −

mysql> update UpdateNumber1To3 set Number=FLOOR(1+RAND()*3);
Query OK, 3 rows affected (0.19 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Let us check the table once again. The query is as follows −

mysql> SELECT *FROM UpdateNumber1To3;

The following is the output with updated value −

+--------+
| Number |
+--------+
| 1      |
| 2      |
| 1      |
+--------+
3 rows in set (0.00 sec)

Chandu yadav

Published on 11-Jan-2019 05:54:41