MySQL UPDATE the corresponding column with random number between 1-3?

For random numbers in a range, you need to use the RAND() method from MySQL. The syntax is as follows for update −

UPDATE yourTableName set yourColumnName=value where yourColumnName2=(SELECT
FLOOR(1+RAND()*3));

In the above query, the statement FLOOR(1+RAND()*3) generates the number between 1-3 and update the column.

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

mysql> create table updateRowWith1To3
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.47 sec)

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

mysql> insert into updateRowWith1To3 values(1,'John');
Query OK, 1 row affected (0.24 sec)

mysql> insert into updateRowWith1To3 values(2,'Sam');
Query OK, 1 row affected (0.14 sec)

mysql> insert into updateRowWith1To3 values(3,'Carol');
Query OK, 1 row affected (0.14 sec)

mysql> insert into updateRowWith1To3 values(4,'Mike');
Query OK, 1 row affected (0.16 sec)

mysql> insert into updateRowWith1To3 values(5,'Bob');
Query OK, 1 row affected (0.10 sec)

mysql> insert into updateRowWith1To3 values(6,'David');
Query OK, 1 row affected (0.17 sec)

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

mysql> select *from updateRowWith1To3;

Output

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    2 | Sam   |
|    3 | Carol |
|    4 | Mike  |
|    5 | Bob   |
|    6 | David |
+------+-------+
6 rows in set (0.00 sec)

Here is the query that can be used to update column name with random number between 1-3 −

mysql> update updateRowWith1To3 set Name='Mary' where Id=(select floor( 1 + RAND( ) *3
));
Query OK, 1 row affected (0.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now you can check the table. The column name would have been updated with Mary between 1-3. The query to check records from the table is as follows −

mysql> select *from updateRowWith1To3;

Output

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | Mary  |
|    2 | Sam   |
|    3 | Carol |
|    4 | Mike  |
|    5 | Bob   |
|    6 | David |
+------+-------+
6 rows in set (0.00 sec)

George John

Published on 10-Jan-2019 12:15:29