Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
701 Views
Advertisements