How to use ORDER BY Clause In SQL

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:

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.

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.