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
How can I loop through all rows of a table in MySQL?
To loop through all rows of a table, use stored procedure in MySQL. The syntax is as follows −
delimiter // CREATE PROCEDURE yourProcedureName() BEGIN DECLARE anyVariableName1 INT DEFAULT 0; DECLARE anyVariableName2 INT DEFAULT 0; SELECT COUNT(*) FROM yourTableName1 INTO anyVariableName1; SET anyVariableName2 =0; WHILE anyVariableName2 < anyVariableName1 DO INSERT INTO yourTableName2(yourColumnName,...N) SELECT (yourColumnName1,...N) FROM yourTableName1 LIMIT anyVariableName2,1; SET anyVariableName2 = anyVariableName2+1; END WHILE; End; //
To understand the above syntax, let us create two tables i.e. one has records and the second table will have records from the loop using stored procedures.
The following is the query to create first table −
mysql> create table AllRows -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.46 sec)
Insert some records in the first table using insert command. The query is as follows −
mysql> insert into AllRows values(1,'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into AllRows values(100,'Carol'); Query OK, 1 row affected (0.13 sec) mysql> insert into AllRows values(300,'Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert into AllRows values(400,'Mike'); Query OK, 1 row affected (0.20 sec)
Display all records from the table using select statement. The query is as follows −
mysql> select *from AllRows;
Output
+------+-------+ | Id | Name | +------+-------+ | 1 | John | | 100 | Carol | | 300 | Sam | | 400 | Mike | +------+-------+ 4 rows in set (0.00 sec)
Here is the query to create a second table. The query to create a table is as follows −
mysql> create table SecondTableRows -> ( -> StudentId int, -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.54 sec)
Now you can loop through all rows of a table using stored procedure. The stored procedure is as follows −
mysql> delimiter // mysql> CREATE PROCEDURE Sp_AllRowsOfATable() -> BEGIN -> DECLARE lastRows INT DEFAULT 0; -> DECLARE startRows INT DEFAULT 0; -> SELECT COUNT(*) FROM AllRows INTO lastRows; -> SET startRows=0; -> WHILE startRows <lastRows DO -> INSERT INTO SecondTableRows(StudentId) SELECT (Id) FROM AllRows LIMIT startRows ,1; -> SET startRows= startRows+1; -> END WHILE; -> End; -> // Query OK, 0 rows affected (0.22 sec) mysql> delimiter ;
Call stored procedure using CALL command. The syntax is as follows −
CALL yourStoredProcedureName;
Call the above stored procedure to loop through all rows of the first table. The query is as follows −
mysql> call Sp_AllRowsOfATable(); Query OK, 1 row affected (0.61 sec)
After calling the stored procedure, let us check what happened with the second table. The query is as follows −
mysql> select StudentId from SecondTableRows;
Output
+-----------+ | StudentId | +-----------+ | 1 | | 100 | | 300 | | 400 | +-----------+ 4 rows in set (0.00 sec)
6K+ Views
