PostgreSQL UPDATE WHERE and IN Operator
Introduction
When you update records in PostgreSQL, you often include a WHERE
clause in your statement to specify conditions that define which rows will get modified. In many of these cases, you might want to update only records where a column’s value can be found in a certain set of values. To handle these situations, you can use the IN
operator in your WHERE
clause. This article will explain how to use the PostgreSQL UPDATE WHERE IN operators and provides some examples to demonstrate how it’s done.
Prerequisites
Before we move forward with this tutorial, let’s review the prerequisites that are necessary for the task:
- You’ll need to have PostgreSQL installed on your computer.
- You’ll also need to have some basic knowledge of PostgreSQL and access to the psql command-line interface for Postgres.
PostgreSQL UPDATE WHERE IN
The PostgreSQL UPDATE
statement is used to change column values within a table. A WHERE
clause included in an UPDATE
statement helps to filter the values that you want to be updated. Within that WHERE
clause, the IN
operator can be used to match multiple values inside the bracket.
Let’s look at the basic syntax of an UPDATE
statement that uses WHERE
and IN
:
1 2 3 | UPDATE TABLE_NAME SET column_name = VALUE WHERE column_name IN ( VALUES ) |
Our first task will be to create a sample table and insert records to it. The statement below can be used to create a table to use in our examples:
1 2 3 4 5 6 | CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product VARCHAR NOT NULL, quantity INT NOT NULL, price NUMERIC NOT NULL ); |
We can then insert some records into the table:
1 2 3 4 5 6 7 8 9 | INSERT INTO products (product, quantity, price) VALUES ('Cheese', '23', '10.28'), ('12 pcs eggs', '92', '2.35'), ('Oranges 1kg', '75', '4.16'), ('Tomato 1kg', '63', '4.26'), ('Wine', '87', '12'), ('Chicken Fillet 1kg', '41', '11.94'), ('Bread', '37', '3'), ('Beer', '53', '4.27'); |
PostgreSQL UPDATE WHERE Example
The following UPDATE
statement will update the product quantity for Cheese
to 0
:
1 2 3 | UPDATE products SET quantity = 0 WHERE product_id IN (1); |
To see if the value has been updated, we can list the records in the table using a simple SELECT
statement:
The output of the statement will look like this:
1 2 3 4 5 6 7 8 9 10 11 | product_id | product | quantity | price ------------+--------------------+----------+------- 2 | 12 pcs eggs | 92 | 2.35 3 | Oranges 1kg | 75 | 4.16 4 | Tomato 1kg | 63 | 4.26 5 | Wine | 87 | 12 6 | Chicken Fillet 1kg | 41 | 11.94 7 | Bread | 37 | 3 8 | Beer | 53 | 4.27 1 | Cheese | 0 | 10.28 (8 ROWS) |
We can see that the quantity for Cheese
was updated to 0
.
PostgreSQL UPDATE WHERE in Example for Multiple Records
When we use the IN
operator as part of a WHERE
clause, we can also match multiple values that are specified inside the parentheses.
Let’s look at the following statement, which will update the price of any products where product_id
is 5
or 6
:
1 2 3 | UPDATE products SET price = price + 1 WHERE product_id IN (5, 6); |
Now, let’s select all the records again:
1 2 3 4 5 6 7 8 9 10 11 | product_id | product | quantity | price ------------+--------------------+----------+------- 2 | 12 pcs eggs | 92 | 2.35 3 | Oranges 1kg | 75 | 4.16 4 | Tomato 1kg | 63 | 4.26 7 | Bread | 37 | 3 8 | Beer | 53 | 4.27 1 | Cheese | 0 | 10.28 5 | Wine | 87 | 13 6 | Chicken Fillet 1kg | 41 | 12.94 (8 ROWS) |
In our next UPDATE
statement, we’ll use a subquery to get the set of values used with the IN
operator. For this example, we’ll add 50
to the quantity of any products where the current quantity is less than 50:
1 2 3 4 5 | UPDATE products SET quantity = quantity + 50 WHERE product_id IN ( SELECT product_id FROM products WHERE quantity < 50 ); |
Let’s execute another SELECT
statement to see which records were updated:
1 2 3 4 5 6 7 8 9 10 11 | product_id | product | quantity | price ------------+--------------------+----------+------- 2 | 12 pcs eggs | 92 | 2.35 3 | Oranges 1kg | 75 | 4.16 4 | Tomato 1kg | 63 | 4.26 8 | Beer | 53 | 4.27 5 | Wine | 87 | 13 7 | Bread | 87 | 3 1 | Cheese | 50 | 10.28 6 | Chicken Fillet 1kg | 91 | 12.94 (8 ROWS) |
We can confirm that the products with a quantity less than 50 are now updated.
Conclusion
When you update records in a PostgreSQL table, you may want to change only the records where a column value matches a certain set of values. Using the UPDATE
statement in conjunction with the WHERE
and IN
operators can help you get the job done. In this article, we explained how to construct a PostgreSQL UPDATE WHERE IN statement and reviewed some examples of this type of statement. With our instructions and examples to get you started, you’ll be prepared to execute an effective UPDATE
statement in your own PostgreSQL environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started