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 different approaches to updating multiple rows effectively using PL/SQL.

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:

1. Using a Single UPDATE Statement
2. Using a Cursor
3. Using MERGE Statement

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. Using a Single UPDATE Statement

The UPDATE statement in 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;

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:

increaseMarks

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. Using a Cursor

For Updating multiple rows at once we can also use Cursors, in 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:

Student	Subject	Marks
John	Math	180
John	Science	160
John	English	190
Jane	Math	170
Jane	Science	150
Jane	English	180

Explanation: Marks for each student-subject combination are doubled.

3. Using 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:

Student	Subject	Marks
John	English	95
Jane	English	95

Explanation: Marks for English subjects are updated to 95 for John and Jane.

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.