How to Update Multiple Rows at Once Using PL/SQL

Understanding Bulk Updates in PL/SQL for Efficient Data Management

Updating multiple rows at once using PL/SQL is a common requirement in real-world database applications. Whether you are processing payroll data, updating order statuses, or modifying customer records in bulk, knowing how to efficiently update multiple rows in PL/SQL can significantly improve performance and maintainability.

This article provides a detailed, beginner-to-intermediate level guide on how to update multiple rows at once using PL/SQL. It covers core concepts, real-world use cases, practical code examples, performance tips, and frequently asked questions.

Why Update Multiple Rows at Once in PL/SQL?

In enterprise applications, databases often contain millions of records. Updating rows one by one can lead to performance bottlenecks, excessive context switching, and increased execution time.

 Use Cases

• Updating employee salaries after annual appraisals
• Changing order statuses in an e-commerce system
• Applying discounts to eligible customers
• Archiving or marking inactive records
• Synchronizing data between systems

Basic UPDATE Statement in PL/SQL

The simplest way to update multiple rows is by using a standard SQL UPDATE statement within PL/SQL. This works well when the same condition and value apply to all affected rows.

Example: Updating Multiple Rows with a WHERE Clause

📄

BEGIN
  UPDATE employees
  SET salary = salary * 1.10
  WHERE department_id = 10;
  
  COMMIT;
END;

Explanation

• All employees in department 10 receive a 10% salary increment
• This method updates multiple rows in a single SQL operation
• Efficient and easy to maintain for uniform updates

Updating Multiple Rows Using a Cursor in PL/SQL

When each row requires different logic or calculations, cursors are commonly used. This approach gives more control but can be slower if not optimized.

Example: Cursor-Based Update

📄

DECLARE
  CURSOR emp_cur IS
    SELECT employee_id, salary
    FROM employees
    WHERE department_id = 20;
BEGIN
  FOR emp_rec IN emp_cur LOOP
    UPDATE employees
    SET salary = emp_rec.salary + 1000
    WHERE employee_id = emp_rec.employee_id;
  END LOOP;
  
  COMMIT;
END;

Applying Discounts to Eligible Customers Using PL/SQL

In retail and e-commerce systems, it's common to provide discounts to customers based on their purchase history, loyalty points, or membership tier. PL/SQL allows us to update multiple rows efficiently to apply these discounts to all eligible customers.

Use Case

• Customers who have purchased more than $500 in the last month are eligible for a 10% discount.
• Loyalty members with more than 100 points receive an additional 5% discount.
• Inactive customers are excluded from discounts.

PL/SQL Example: Applying Discounts

📄

DECLARE
  TYPE cust_tab IS TABLE OF customers.customer_id%TYPE;
  eligible_customers cust_tab;
BEGIN
  -- Collect eligible customers
  SELECT customer_id
  BULK COLLECT INTO eligible_customers
  FROM customers
  WHERE total_purchase > 500
    AND loyalty_points >= 100
    AND status = 'ACTIVE';

  -- Apply discount using FORALL
  FORALL i IN eligible_customers.FIRST .. eligible_customers.LAST
    UPDATE customers
    SET discount_percentage = 15
    WHERE customer_id = eligible_customers(i);

  COMMIT;
END;

Explanation

• The BULK COLLECT statement retrieves all eligible customers into a PL/SQL collection.
• The FORALL statement applies the 15% discount to all eligible customers in bulk, improving performance.
• The COMMIT ensures that all changes are saved to the database at once.

Benefits of This Approach

• Efficiently updates multiple customer records without looping row by row.
• Minimizes SQL and PL/SQL context switches.
• Easy to modify conditions for eligibility or discount percentage.
• Ensures consistency and scalability for large datasets.

Efficient Bulk Updates Using FORALL in PL/SQL

The FORALL statement is one of the most powerful features for updating multiple rows at once using PL/SQL. It minimizes context switching and improves performance.

Example: PL/SQL FORALL Update

📄

DECLARE
  TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
  emp_ids emp_id_tab;
BEGIN
  SELECT employee_id
  BULK COLLECT INTO emp_ids
  FROM employees
  WHERE department_id = 30;

  FORALL i IN emp_ids.FIRST .. emp_ids.LAST
    UPDATE employees
    SET salary = salary + 500
    WHERE employee_id = emp_ids(i);

  COMMIT;
END;

Using BULK COLLECT and FORALL Together

Combining BULK COLLECT and FORALL is a best practice for mass updates when working with thousands of records.

 Scenario

Suppose a retail system needs to update loyalty points for customers based on recent purchases.

📄

DECLARE
  TYPE cust_tab IS TABLE OF customers.customer_id%TYPE;
  cust_ids cust_tab;
BEGIN
  SELECT customer_id
  BULK COLLECT INTO cust_ids
  FROM customers
  WHERE total_purchase > 5000;

  FORALL i IN cust_ids.FIRST .. cust_ids.LAST
    UPDATE customers
    SET loyalty_points = loyalty_points + 100
    WHERE customer_id = cust_ids(i);

  COMMIT;
END;

Comparison of Update Methods in PL/SQL

Updating Multiple Records in PL/SQL

• Use FORALL for large-scale updates
• Commit only after successful completion
• Handle exceptions for bulk operations
• Avoid row-by-row processing when possible
• Test performance in realistic environments
• Using cursors unnecessarily for simple updates
• Committing inside loops
• Ignoring exception handling in bulk updates
• Not indexing columns used in WHERE clauses

Learning how to update multiple rows at once using PL/SQL is essential for building scalable and high-performing Oracle database applications. From simple UPDATE statements to advanced FORALL bulk updates, each method has its place depending on the business requirement.By using PL/SQL bulk update techniques, you can reduce execution time, improve performance, and write cleaner, more maintainable code.

Frequently Asked Questions (FAQs)

1. What is the best way to update multiple rows in PL/SQL?

The best approach depends on the scenario. For uniform updates, use a single UPDATE statement. For large datasets with row-specific logic, use FORALL with BULK COLLECT.

2. Is FORALL faster than a cursor loop in PL/SQL?

Yes, FORALL is significantly faster because it reduces context switching between SQL and PL/SQL engines.

3. Can I use FORALL with complex conditions?

FORALL works best when conditions are pre-filtered using BULK COLLECT. Complex logic can be handled before the bulk operation.

4. How many rows can BULK COLLECT handle?

BULK COLLECT can handle large volumes, but memory limitations apply. For very large datasets, consider using LIMIT with BULK COLLECT.

5. Do I need to commit after updating multiple rows in PL/SQL?

Yes, a COMMIT is required to make changes permanent, but it should be executed after the bulk operation, not inside loops.