PostgreSQL Update Limit

As a database developer, I recently got the opportunity to work with PostgreSQL Update Limit. In this PostgreSQL tutorial, I am going to show you how to use Postgres Update Limit.

You will understand how to update a certain number of records or rows of the table using the keyword ‘LIMIT’ with the ‘UPDATE’ statement.

Postgres Update Limit

You always use the ‘UPDATE” statement to modify or change the columns value of the rows in the table. When the ‘UPDATE’ statement is used, it changes the value of all the columns based on the given condition in the ‘WHERE’ clause or wherever the condition matches the column’s value.

Sometimes you need to make modifications to only certain columns of the rows. In that case, you will use the ‘UPDATE LIMIT’ clause.

Syntax

The syntax is given below.

UPDATE table_name
SET column_name = new_value
LIMIT n_rows;

• table_name: The name of the table that contains columns for column value updation.
• column_name: The name of the column whose value needs to be updated.
• new_value: This is the new value that is assigned in place of the old value of the column.
• LIMIT n_rows: The number of rows should be affected by the updation.

Let’s understand how to use the ‘UPDATE LIMIT’ by using the examples below.

Approach-1 Using Condition

You can make updates to a limited number of columns of rows based on the specified conditions in the ‘WHERE’ clause.

For example, you have a table called ’employees’ with columns ’employee_id’, ‘name’, ‘department_id’, and ‘salary’.

Now, you need to increase the salary by $500 for the employee whose current salary is greater than $55000. For this, use the command below.

UPDATE employees
SET salary = salary + 500
FROM (
    SELECT department_id
    FROM employees
    WHERE salary > 50000
    ORDER BY department_id
    LIMIT 1
) AS employees_sal_update
WHERE 
employees.department_id = employees_sal_update.department_id;

SELECT * FROM employees;

After executing the query below, I obtained the expected output. Look at the screenshot below for your reference.

Look at the above query, which first retrieves the employee’s department_id whose salary is greater than $ 50,000 from the subquery. Then, updating the salary of the employee by $500 in the main query based on the condition specified in the ‘WHERE’ clause.

Within the subquery, the ‘LIMIT’ clause is used to limit the rows based on the ‘department_id’. This query returns the department as equal to 1. In the table, there are two employees with the same department ID, which is why their salaries are updated accordingly.

Approach-2 Using the First N Rows

You can also update the first n rows of the table using the ‘UPDATE LIMIT’ clause. Suppose you have a table called ‘customers’ with columns ‘first_name’, ‘last_name’, ‘country’, ‘account_status’, and ‘purchase_history’.

Currently, in the table ‘customers’, each employee is in an active state as shown in the picture below.

Suppose you need to set the account status of the first three employees to inactive. Use the command below.

UPDATE customers
SET account_status = 'inactive'
WHERE id IN 
(SELECT id FROM customers
ORDER BY id
LIMIT 3);

In the above query, get the first three employees’ id using the subquery. Then, in the main query, update the account status of those 3 employees to inactive.

In the subquery, ‘LIMIT’ is used to limit the returned results to only 3 rows, which means this returns only the ‘id’ of the first 3 employees of the table customers.

Now, recheck the table using the query below.

SELECT * FROM customers
ORDER BY id;

After executing the above query, I got the expected output as shown in the screenshot below.

In the above output, you can see the first three employees ‘Michael’, ‘Emma’, and ‘Robert’ account statuses set to inactive.

Approach-3 Using Subqueries

We can also use the subqueries as mentioned in the query below.

UPDATE productorders
SET status = 'processing'
WHERE order_id IN (
    SELECT order_id 
    FROM productorders
    WHERE status = 'pending'
    ORDER BY order_date ASC
    LIMIT 500
);

After executing the above query, I got the expected output as shown in the screenshot below.

Approach-4 Using Common Table Expressions

We can use the following query for this purpose.

WITH rows_to_update AS (
    SELECT order_id 
    FROM productorders
    WHERE status = 'pending'
    ORDER BY order_date DESC
    LIMIT 100
)
UPDATE productorders
SET status = 'processing'
WHERE order_id IN (SELECT order_id FROM rows_to_update);

After executing the above query, I got the expected output as shown in the screenshot below.

Conclusion

In this PostgreSQL tutorial, you have learned how to update the column value of only specific rows of the table using the ‘UPDATE LIMIT’ statement. Also learned about the syntax of using Postgres Update Limit, then discussed how to update the first n rows using the ‘UPDATE LIMIT’ statement.

You may like to read:

• How to use PostgreSQL current_timestamp
• How to use Postgresql group_concat
• PostgreSQL RANK Function
• How To Concat In PostgreSQL
• PostgreSQL Compute Total Sum of Column
• PostgreSQL ORDER BY COUNT