PostgreSQL UPDATE WHERE and IN Operator

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.