How to select random values from a table and store it in another table in mysql

• Summary
• How to select random values from a table
• select random values from a table and store it in another table in mysql
• Conclusion

Summary

In this tutorial we will learn how to select random values from a table and store it in another table in mysql.

To retrieve random values from a table we use rand() function in mysql. rand() function fetches random values from a table. Lets take an example.

How to select random values from a table

We have one table called department. Now we want to retrieve all records randomly we use rand() function.

Also read :How to install latest phpMyAdmin on CentOS 7

 mysql> select * from department order by rand() ;

Here order by clause is used to sort the randomly generated data.

Now we can place the limit to fetch particular no of rows like the below query;

Also read :INSERT ,UPDATE and DELETE Query in MySQL

mysql> select * from department order by rand() limit 3;

Here we place limit 3 to fetch 3 rows instead of 8 rows.

select random values from a table and store it in another table in mysql

Now we are going to store our randomly generated records to another table. we have department table from which we have fetched our random records. Now we will create another table named backupdept and in this table we are going to the fetched data.

Now to create table in mysql we need to execute the below query.

mysql> create table backupdept ( deptno int ,deptname varchar(20),ename varchar(20),sal int);

Query OK, 0 rows affected (0.03 sec)

To insert data from one table to another table we will use insert into select statement as below.

Also read :How to Create Database,Table In MySQL

mysql> insert into backupdept (deptno,deptname,ename,sal) select  * from department order by rand() ;

Conclusion

In this tutorial we have learned how to select random values from a table and store it in another table in mysql.

Also read :How to create and show table in mysql database using python on CentOS and RHEL 6/7 step by step guide

SHARE

Admin

• 
• 
• 
• 
•