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.

These are following topics that are covered to show how to use the ‘UPDATE LIMIT’:

• Postgres Update Limit
• Using Condition
• Using First N Rows

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.

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 below examples.

Postgres Update Limit 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 of the employee whose current salary is greater than $55000. For that use the below command.

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;

Look at the above query, first getting the employee ‘department_id’ whose salary is greater than $50000 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 ‘LIMIT’ is used to limit the rows for the ‘department_id’. This query returns the department as equal to 1. So in the table, there are two employees with the same department id, this is why the salary is updated for both employees.

Postgres Update Limit using 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 below picture.

Suppose you need to set the account status of the first 3 employees to inactive, use the below command.

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 that 3 employees to inactive.

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

Now check the table again using the below query.

SELECT * FROM customers
ORDER BY id;

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

Conclusion

In this PostgreSQL tutorial, you have learned how to update the column value of only certain 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