How can I loop through all rows of a table in MySQL?

MySQLMySQLi Database

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)
raja
Published on 10-Jan-2019 15:26:29
Advertisements