How to Update Top 100 Records in PL/SQL?

In terms of database management, the ability to update specific subsets of data is crucial for maintaining system integrity and meeting user needs. In this article, we will understand two primary methods for updating top records. Using the ROWNUM function and Using the ORDER BY clause. Each method is explained in detail, including syntax, and examples.

Introduction to Update Statement in PL/SQL

The UPDATE statement in PL/SQL is a powerful tool used to modify existing records in a database table. It allows developers to make changes to one or more columns within a specified table based on specific criteria, such as conditions defined in the WHERE clause.

Basic Syntax for the UPDATE statement:

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

Explanation:

• table_name is the name of the table from which records will be updated.
• column1, column2, etc., represent the columns within the table that will be updated.
• value1, value2, etc., are the new values to be assigned to the respective columns.
• The WHERE clause is optional but recommended, as it allows you to specify conditions that must be met for the update to occur. If omitted, all records in the table will be updated.

Note: For demonstration, WE will only update the top 2 rows. However, the steps will be the same irrespective of the number of rows.

Setting Up Environment

Let us start by creating a table and adding some sample data to the table.

CREATE TABLE employees
( 
    employee_id number(10) NOT NULL,
    employee_name varchar2(50) NOT NULL,
    city varchar2(50)
);

INSERT INTO employees
(employee_id, employee_name, city)
SELECT 1, 'Jack', 'New York' FROM DUAL
UNION ALL
SELECT 2, 'Jill', 'Los Angeles' FROM DUAL
UNION ALL
SELECT 3, 'Jim', 'Las Vegas' FROM DUAL
UNION ALL
SELECT 4, 'Bill', 'New York' FROM DUAL
UNION ALL
SELECT 5, 'Ben', 'Los Angeles' FROM DUAL
UNION ALL
SELECT 6, 'Alex', 'Las Vegas' FROM DUAL
UNION ALL
SELECT 7, 'Andrew', 'New York' FROM DUAL
UNION ALL
SELECT 8, 'Chris', 'Los Angeles' FROM DUAL;

We will use the following query to print the initial data in the table:

Query:

Let’s write an query to display employee information from the employees table in a structured format. The script should iterate through each employee record and show their employee_id and employee_name.

DECLARE
    r_emp employees%ROWTYPE;

    CURSOR emp_cur is
    SELECT *
    FROM employees;
BEGIN
    FOR r_emp IN emp_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE( 'id: ' || r_emp.employee_id ||', name: ' || r_emp.employee_name || ', city: ' || r_emp.city );
    END LOOP;
END;

Output:

Initial data

We are going to have a look at two methods in this article to go about updating the top n records in the table.

Ways to Update Top 100 Records

Method 1: Using ROWNUM Function

The ROWNUM function returns the row number of the current record in the table or joined tables. The numbering starts from 1, so the first record has ROWNUM value 1, the second record has ROWNUM value 2 and so on.

We can use the ROWNUM function to filter the records which have row number less than 100 and then update them using the UPDATE clause.

The following query sets the city of the top 2 employees to be London.

Query:

UPDATE employees
SET city='London'
WHERE ROWNUM <= 2;

Output:

Output

Explanation: In the above query we have UPDATE or modifies the ‘city‘ column for the first two rows in the ‘employees‘ table, setting their value to ‘London‘. It uses the ROWNUM pseudo–column to limit the update to the specified number of rows.

Method 2: Using ORDER BY Clause

As we can see the above method only updates the top n records but the ordering is dependent on the insertion order. We might want to update the records based on some ordering. For this purposes, we can use the ORDER BY clause in conjunction with ROWNUM function.

In the following query, we use a subquery to first order the fields using ORDER BY clause and then filter the necessary records using ROWNUM function and WHERE clause. Finally we use the UPDATE clause to set the city to be Paris of the desired records.

Query:

UPDATE employees
SET city='Paris'
WHERE employee_id in 
(
    SELECT employee_id FROM (
        SELECT employee_id, employee_name FROM employees
        ORDER BY employee_name
    )
    WHERE ROWNUM <= 2 
);

Output:

Output

Explanation: The UPDATE statement assigns the value ‘Paris‘ to the ‘city‘ column for the first two employees in the ‘employees‘ table, ordered by their names. It uses a subquery to select the employee IDs and names sorted alphabetically and limits the update to the first two rows returned by the subquery using ROWNUM.

Conclusion

Updating top records in PL/SQL is a important aspect of data manipulation.In this article we have understand two effective methods for achieving this task: utilizing the ROWNUM function and ORDER BY clause. By understanding and applying these methods, developers can efficiently manage data updates, ensuring systems remain adaptable and responsive to changing requirements.