SQL is a powerful language used to manage and manipulate relational databases. One of the most common tasks in SQL is to iterate over a set of rows in a table and perform some action on each row. To accomplish this, SQL provides a loop construct called the for loop. In this tutorial, we will learn how to use the for loop in SQL.
Syntax of the For Loop
The syntax of the for loop in SQL is as follows:
FOR variable IN (start..end) LOOP -- statements to be executed on each iteration END LOOP;
variable is the loop variable that takes values from
end. The statements between
END LOOP are executed on each iteration of the loop.
Let’s consider a simple example to illustrate the use of the for loop in SQL. Suppose, we have a table called
employees with the following structure:
Suppose, we want to increase the salary of each employee by 10%. We can achieve this using a for loop in SQL as follows:
FOR emp IN (SELECT * FROM employees) LOOP UPDATE employees SET salary = emp.salary*1.1 WHERE id = emp.id; END LOOP;
Here, we have used a for loop to iterate over all the rows in the
employees table. On each iteration, we have updated the
salary column by multiplying it by 1.1 (i.e., increasing it by 10%).
The output of the above SQL code will be as follows:
As we can see, the salary of each employee has been increased by 10%.
- Make sure to specify the correct loop variable in the
UPDATEstatement to avoid updating all rows in the table.
- Ensure that the
SELECTstatement in the loop header returns only the rows that need to be updated.
In this tutorial, we learned how to use the for loop in SQL to iterate over a set of rows in a table and perform some action on each row. The for loop is a powerful construct that can help us automate repetitive tasks in SQL. By following the syntax and examples provided in this tutorial, you should now be able to use the for loop in your SQL queries.