dark

How to Use SQL for Loop

How to Use SQL for Loop

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;

Here, variable is the loop variable that takes values from start to end. The statements between LOOP and END LOOP are executed on each iteration of the loop.

Example

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:

idnamesalary
1Alice50000
2Bob60000
3Charlie70000

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%).

Output

The output of the above SQL code will be as follows:

idnamesalary
1Alice55000
2Bob66000
3Charlie77000

As we can see, the salary of each employee has been increased by 10%.

Troubleshooting Tips

  • Make sure to specify the correct loop variable in the UPDATE statement to avoid updating all rows in the table.
  • Ensure that the SELECT statement in the loop header returns only the rows that need to be updated.

Conclusion

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.

Related tutorials