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:

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:

We can then insert some records into the table:

PostgreSQL UPDATE WHERE Example

The following UPDATE statement will update the product quantity for Cheese to 0:

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:

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:

Now, let’s select all the records again:

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:

Let’s execute another SELECT statement to see which records were updated:

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.