How to Loop Through All Rows of Table in MySQL

Most database tasks are handled by set based queries such as SELECT, UPDATE, INSERT, etc. but sometimes you may need to loop through all rows of table in MySQL. This is commonly required in stored procedures and functions. In this article, we will learn how to do this.

How to Loop Through All Rows of Table in MySQL

For our example, we will be using two tables A(id, sale) and B(id, sale). We will be selecting rows from table A one by one and inserting them into table B.

Although this can be easily done using an INSERT INTO…SELECT statement, we will do this using loops for the sake of understanding how loops work in MySQL.

Here is a simple logic we are trying to implement.

for(each row in A as rowA)
{
  insert into B(ID, SALE) values(rowA[ID], rowA[SALE]);
}

First we will drop any stored procedure with existing name to avoid future conflict, and also change delimiter to avoid running each statement of stored procedure as we type it.

DROP PROCEDURE IF EXISTS LOOP_ROW;
DELIMITER ;;

Here is our stored procedure.

CREATE PROCEDURE LOOP_ROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM A INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO B(ID, VAL) SELECT (ID, VAL) FROM A LIMIT i,1;
  SET i = i + 1;
END WHILE;
End;
;;

In the above code, we declare two variables i and n, where n holds the count of rows in A, and is the loop counter that starts from 0 and increments by 1 after each iteration.

We implement the loop using while statement with condition i<n. It is located between while i<n do… end while. In each iteration we run the statement to select 1 row from table A and insert it into table B.

  INSERT INTO B(ID, VAL) SELECT (ID, VAL) FROM A LIMIT i,1;

You can replace this statement with one or more statements that you want to execute in each iteration. Then we reset the delimiter back to ;

DELIMITER ;

Here is how we call this stored procedure.

CALL LOOP_ROW();

If you already know the number of iterations (e.g. 25) you can simplify the initial variable declarations as

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SET i=0;
SET n=25;

In this article, we have learnt how to loop through all rows in MySQL. You can customize it as per your requirement.

How to Loop Through All Rows of Table in MySQL

How to Loop Through All Rows of Table in MySQL

Related posts:

Leave a Reply Cancel reply