How to Update Multiple Rows at Once Using PL/SQL?

Updating multiple rows simultaneously is a common requirement in database management, especially when handling large datasets. PL/SQL, the procedural extension of SQL in Oracle databases, provides various techniques to accomplish this task efficiently.

In this article, we will explore three powerful Oracle techniques: using a single UPDATE statement, employing cursors, and leveraging the MERGE statement. Each method offers unique advantages, from direct bulk updates to iterative and condition-based updates, making Oracle PL/SQL a highly flexible tool for data management.

How to Update Multiple Rows in PL/SQL?

The UPDATE statement is a type of statement PL/SQL that helps to change or update the already existing data from the table. Let's explore some of the common approaches to Update Multiple Rows. The common approaches are as follows:

We can create the Students' table using the following code which defines the table structure with columns such as 'Student,' 'Subject,' and 'Marks,' as Columns.

CREATE TABLE Students 
(
    Student VARCHAR(50),
    Subject VARCHAR(50),
    Marks INT
);

INSERT INTO Students (Student, Subject, Marks)
VALUES
    ('John', 'Math', 90),
    ('John', 'Science', 80),
    ('John', 'English', 95),
    ('Jane', 'Math', 85),
    ('Jane', 'Science', 75),
    ('Jane', 'English', 90);

1. Updating Multiple Rows with a Single UPDATE Statement

The UPDATE statement in Oracle PL/SQL enables the modification of multiple rows in a single operation. By specifying the columns to update and the new values, along with optional conditions, administrators can perform bulk updates efficiently.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

here,

• UPDATE: Specifies the table to modify.
• SET: Identifies the columns to update and assigns new values.
• WHERE: Filters rows, ensuring only targeted records are updated. Without WHERE, all rows in the Oracle table will be updated.

Example: UPDATE Marks for Students Table

Now, we want to increase the marks of the students who scored less than 90 by 5. We can do this by using following query.

Query:

UPDATE students 
SET marks = marks + 5
WHERE marks < 90;

Output:

Explanation: Here, we can see that the marks value has increased by 5 for those rows where the marks value was less than 90.

2. Updating Multiple Rows Using a Cursor

For Updating multiple rows at once we can also use Cursors. In Oracle PL/SQL cursor can provide a mechanism for iterating over query results and performing updates iteratively.

Syntax

DECLARE
    CURSOR cursor_name IS
        SELECT columns FROM table_name WHERE conditions;
BEGIN
    FOR record_name IN cursor_name LOOP
        -- Update operation using record_name.field_name
        -- Example: record_name.column_name := new_value;
    END LOOP;
END;

Example: Marks Update for Each Student

In this example, a PL/SQL cursor named update_cursor is declared to fetch all the records from the "Students" table. The purpose of this cursor is to iterate over the query results and perform an update operation on each row.

Query:

DECLARE
    CURSOR update_cursor IS
        SELECT * FROM Students;
BEGIN
    FOR record IN update_cursor LOOP
        UPDATE Students
        SET Marks = record.Marks * 2
        WHERE Student = record.Student AND Subject = record.Subject;
    END LOOP;
END;

Output:

Explanation: Here, the cursor in Oracle fetches each row, and for each row, an UPDATE operation doubles the Marks value. The loop iterates through all records, updating them one by one.

3. Updating Rows Using the MERGE Statement

The MERGE statement combines the functionality of INSERT, UPDATE, and DELETE operations, making it efficient for updating multiple rows based on conditions.

Syntax

MERGE INTO target_table USING source_table
ON (merge_condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (value1, value2, ...);

Example: Update Marks for English Subjects

In this example, the MERGE statement is utilized to update the marks for English subjects for specific students, John and Jane.

Query:

MERGE INTO Students s
USING (
    SELECT 'John' AS Student, 'English' AS Subject FROM DUAL
    UNION ALL
    SELECT 'Jane', 'English' FROM DUAL
) new_data
ON (s.Student = new_data.Student AND s.Subject = new_data.Subject)
WHEN MATCHED THEN
    UPDATE SET s.Marks = 95
WHEN NOT MATCHED THEN
    INSERT (Student, Subject, Marks)
    VALUES (new_data.Student, new_data.Subject, 95);

Output:

Explanation:

• The MERGE statement checks if Student and Subject match any records in the Students table.
• If a match is found, it updates the Marks column to 95.
• If no match is found, it inserts a new record with the specified values

Conclusion

Overall, the UPDATE statement in PL/SQL offers a direct and efficient method of updating multiple rows in a table at once. By using the UPDATE command with conditions the database administrators and developers can easily edit and manage data ensuring fast operations and accurate record updates. Because of its simplicity and efficiency the UPDATE statement is a fast tool for improving data management in PL/SQL environments.