How to add a random number between 30 and 300 to an existing field in MySQL?

MySQLMySQLi Database

Let us first create a demo table

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

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

mysql> insert into RandomNumberDemo values(17);
Query OK, 1 row affected (0.20 sec)
mysql> insert into RandomNumberDemo values(18);
Query OK, 1 row affected (0.12 sec)
mysql> insert into RandomNumberDemo values(29);
Query OK, 1 row affected (0.49 sec)

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

mysql> select *from RandomNumberDemo;

The following is the output

+----------+
| MyNumber |
+----------+
|       17 |
|       18 |
|       29 |
+----------+
3 rows in set (0.00 sec)

Here is the query to add a random number between 30 and 300 to an existing field

mysql> update RandomNumberDemo set MyNumber=MyNumber+ FLOOR(RAND() * (270 + 1)) + 30 where MyNumber=18;
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

mysql> select *from RandomNumberDemo;

The following is the output

+----------+
| MyNumber |
+----------+
|       17 |
|      158 |
|       29 |
+----------+
3 rows in set (0.00 sec)
raja
Published on 01-Apr-2019 11:17:58
Advertisements