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.
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:
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.