

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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)
- Related Questions & Answers
- MySQL update with random number between 1 - 3
- How to update MySQL column with random value?
- How to update records in a column with random numbers in MySQL?
- How to return a random number between 1 and 200 with JavaScript?
- MySQL CONCAT a specific column value with the corresponding record
- How to update column values with date records and set 1 for corresponding records before the current date in SQL
- MySQL query to display only the column values with corresponding column having whitespace
- Sum of the series 1 + (1+3) + (1+3+5) + (1+3+5+7) + + (1+3+5+7+....+(2n-1)) in C++
- Sum of the series 1 + (1+3) + (1+3+5) + (1+3+5+7) + ...... + (1+3+5+7+...+(2n-1)) in C++
- Update a MySQL column with JSON format?
- Update a column of text with MySQL REPLACE()
- Sum of the series 1 + (1+2) + (1+2+3) + (1+2+3+4) + ... + (1+2+3+4+...+n) in C++
- Java Program to generate custom random number -1 or 1
- Order randomly in MySQL with a random value column?
- Add records from corresponding duplicate values in another column with MySQL
Advertisements