How to Update Top 100 Records in PostgreSQL?

PostgreSQL is one of the most advanced general-purpose object-relational database management systems and is open-source. Being an open-source software, its source code is available under the open-sourcePostgreSQL license, a liberal open-source license. Anyone with the right skills is free to use, modify, and distribute PostgreSQL in any form. As it is highly stable, very low effort is required to maintain this DBMS. In this article, we will be looking at how one can update the top 100 records using PostgreSQL. Understanding this will allow developers to easily manipulate data in their existing tables.

Introduction to Updating Records in PostgreSQL

Updating records in a PostgreSQL database involves modifying existing data to reflect changes or corrections. This operation is essential for maintaining data integrity, ensuring accuracy, and keeping information up to date. Generally, UPDATE Statement is used to update or change the already existing record to new records.

Setting Up Environment

Let us start by creating a table and adding some sample data to the table. We will create a test table with an id and title field. The following query creates the table:

CREATE TABLE test(id INTEGER, title VARCHAR(100));

Now that we have the table, let’s insert some data into the data. The following query inserts ten records in the test table:

INSERT INTO test (id, title)
VALUES
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3'),
(4, 'Title 4'),
(5, 'Title 5'),
(6, 'Title 6'),
(7, 'Title 7'),
(8, 'Title 8'),
(9, 'Title 9'),
(10, 'Title 10');

Output:

Initial Data

Now that we have the setup in place, lets now go forward to see how we can update the top 100 records of the table. We are going to have a look in this article at two methods to update the top 100 records of the table.

Ways to Update Top Records in PostgreSQL

Method 1: Using Subquery

The LIMIT clause is used to restrict the records returned by the query. We can make use of the LIMIT clause in conjunction with UPDATE clause to restrict the query to only update the top n records. However, to make this work, we need to provide an ordering using the ORDER BY clause for the SQL engine to understand what records to return.

The following query updates the top 3 records depending on the ascending order of the id column:

Query:

UPDATE test SET id=-10 
WHERE id IN 
(
  SELECT id FROM test
  ORDER BY id
  LIMIT 3
);

Output:

Updated data

Explanation: The following image shows the records in the table after the update command is executed.

Method 2: Using CTE

We can make use of Common Table Expression (CTE) to select only the top n records from the table and later update them using the UPDATE clause. However, to make this work, we need to provide an ordering using the the clause for the SQL engine to understand what records to return.

The following query first creates a CTE of the top 3 records ordered by the id field. The update statement uses the CTE to update the top 3 records as above.

Query:

WITH CTE AS 
(
   SELECT id, row_number() OVER (ORDER BY id NULLS LAST) AS rn
   FROM   test    
   ORDER  BY id NULLS LAST
   LIMIT  3
   )
UPDATE test t
SET    id = -10
FROM   CTE
WHERE  CTE.id = t.id;

Output:

Updated data

Explanation: The following image shows the records in the table after the update command is executed.

Technical Example

Let’s now use the concepts we have learned in this article in a technical example.

First let’s create the table and insert some data inside it. The following query creates an employee table and inserts nine records in it.

Query:

-- create
CREATE TABLE EMPLOYEE 
(
  empId int,
  name varchar(100),
  dept varchar(50)
);
-- insert
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (100, 'Clark', 'Engineering');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (5, 'Jill', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (6, 'Ava', 'Marketing');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (71, 'Tom', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (4, 'Jake', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (8, 'Ben', 'Marketing');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (9, 'Alice', 'Engineering');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (11, 'Mike', 'Marketing');

Output:

Initial data

Explanation: The above query creates an employee table with three fields and inserts the data for nine employees. The following query returns the initial data in the table.

Now let’s try to update the department of the top 3 employees ordered by employee id to ‘Analytics‘. For this we are going to make use of the concepts learned in method 2. We will use make use of the CTE to get the desired results. In the following query, we make use of ORDER BY clause to order the employees by their employee id and the LIMIT clause in the end to restrict the update statement to the top 3 records:

Query:

WITH CTE AS 
(
   SELECT empId, row_number() OVER (ORDER BY empId NULLS LAST) AS rn
   FROM   EMPLOYEE    
   ORDER  BY empId NULLS LAST
   LIMIT  3
   )
UPDATE EMPLOYEE e
SET dept='Analytics'
FROM  CTE
WHERE CTE.empId = e.empId;

Output:

Updated data

Explanation: In the output as we can the department of Dave, Jake and Jill has been updated to Analytics from Accounting, Sales respectively.

Conclusion

In this article we have understand that how to update n records in the table. We have seen the two method through which we can update the n records. The methods are Using Subquery and Using Common Text Expression(CTE). We have also saw examples of how do it. Now you can easily update the n number of record with the help of these method easily.