How to Use the WHILE Loop in MySQL

08/06/2021

Contents

In this article, you will learn how to use the WHILE loop in MySQL.

Using the WHILE loop in MySQL

In MySQL, the WHILE loop is a control flow statement that allows you to execute a block of code repeatedly as long as a specified condition is true. This loop can be useful in a wide range of situations, such as performing iterative calculations, updating or modifying data in a database, and more.

Syntax

The basic syntax of the WHILE loop in MySQL is as follows:

WHILE condition DO
  statements;
END WHILE;

Here, “condition” is the condition that you want to test, and “statements” is the block of SQL statements that will be executed repeatedly as long as the condition is true.

Examples

Using WHILE to perform iterative calculations

Suppose we want to calculate the sum of the first 10 numbers using the WHILE loop in MySQL. We can do this as follows:

SET @sum = 0;
SET @counter = 1;

WHILE @counter <= 10 DO
   SET @sum = @sum + @counter;
   SET @counter = @counter + 1;
END WHILE;

SELECT @sum;

In the above example, we initialize two variables, @sum and @counter, to 0 and 1 respectively. We then use the WHILE loop to repeatedly add the value of @counter to @sum, and increment @counter by 1, until @counter is greater than 10. Finally, we print the value of @sum.

Using WHILE to update data in a database

Suppose we have a table called "students" that contains information about students, including their name, age, and grade. We want to update the grade of all students who are under the age of 18 to "A", and all students who are 18 or older to "B". We can use the WHILE loop to accomplish this as follows:

SET @counter = 0;

WHILE @counter < (SELECT COUNT(*) FROM students) DO
   SET @counter = @counter + 1;
   UPDATE students SET grade = IF(age < 18, 'A', 'B') WHERE id = @counter;
END WHILE;

In the above example, we initialize a variable, @counter, to 0. We then use the WHILE loop to iterate over each student in the "students" table, and update their grade based on their age. Finally, we increment the value of @counter by 1, until all students have been processed.

Using WHILE to create a temporary table

Suppose we have a table called "sales" that contains information about sales made by salespeople, including their name and the amount of sales. We want to create a temporary table that summarizes the sales made by each salesperson, including their total sales, average sales, and number of sales. We can use the WHILE loop to accomplish this as follows:

DROP TABLE IF EXISTS temp_sales_summary;

CREATE TEMPORARY TABLE temp_sales_summary (
   name VARCHAR(50),
   total_sales DECIMAL(10, 2),
   average_sales DECIMAL(10, 2),
   number_of_sales INT
);

SET @counter = 0;

WHILE @counter < (SELECT COUNT(DISTINCT name) FROM sales) DO
   SET @counter = @counter + 1;
   INSERT INTO temp_sales_summary (
      SELECT name, SUM(amount), AVG(amount), COUNT(*) FROM sales WHERE name = (SELECT DISTINCT name FROM sales LIMIT @counter - 1, 1)
   );
END WHILE;

SELECT * FROM temp_sales_summary;

In the above example, we first drop any existing temporary table called "temp_sales_summary". We then create a new temporary table with three columns, "name", "total_sales", "average_sales", and "number_of_sales".

We initialize a variable, @counter, to 0. We then use the WHILE loop to iterate over each unique salesperson in the "sales" table, and calculate their total sales, average sales, and number of sales. Finally, we insert this information into the temporary table. We increment the value of @counter by 1, until all salespeople have been processed.

We then print the contents of the temporary table using a SELECT statement.

#MySQL