How to use PostgreSQL UPDATE WHERE on an ObjectRocket Instance

Introduction

The PostgreSQL UPDATE clause is used to modify records that already exist in a table. Using the WHERE clause in conjunction with the UPDATE clause will target, and update, only the rows that are selected. If the WHERE clause is not used when executing the PostgreSQL UPDATE function, all the rows in the table will be updated. This tutorial will provide explanations and examples on how to use PostgreSQL UPDATE WHERE on a ObjectRocket instance.

Prerequisites

The following software programs must be correctly installed and configured on the local operating system in order to use the PostgreSQL UPDATE WHERE on a ObjectRocket instance:

• PostgreSQL 11

• An instance of Postgres for an ObjectRocket account must be created in the ObjectRocket Mission Control panel. For reference, an image of the panel follows:

How to Connect to PostgreSQL Instance

First, establish a connection to the remote Postgres instance on ObjectRocket by executing the following command:

Following is an alternative way of connecting to the remote Postgres instance using the terminal:

PostgreSQL UPDATE WHERE Statement

The UPDATE statement when combined with WHERE clause will perform different functions in updating a specific record. This is because the query needs to adhere to the specified criteria set in the WHERE clause before it can proceed with the update process.

Here is an example of a Postgres UPDATE WHERE statement:

Following is an explanation of the above UPDATE WHERE function:

• First, the statement UDPATE is specified and then followed by the name of the table to update.
• Second, the SET clause is used against the column or columns, that are separated by commas, where the values are to be update.
• Third, the WHERE clause is used to define the condition that refines how the update process will be executed.

Sample Data Set

Following is the sample grocery_list data set that will be used for demonstrating the examples in this tutorial:

PostgreSQL Update Single Table Column

In this first example, a single record will be updated within thegrocery_list table. Here the easiest approach is to use the id as the criteria for the WHERE clause, as shown in the following statement:

After executing the above command, Postgres will provide the following notification: UPDATE 1.

Now verify the result using the following SELECT statement:

PostgreSQL Update Multiple Table Column.

Multiple columns can also be udated via the UPDATE WHERE clause by providing multiple comma-delimited values accordingly to the SET clause.

The following example will update all the prices to ‘$8’ of only the records that have a quantity of ten (10) and increase the same records by two (2), making the quantity columns equals to twelve (12):

The result should resemble the following:

Conclusion

This tutorial provided explanations and examples on how to use PostgreSQL UPDATE WHERE on a ObjectRocket instance. The tutorial first covered two ways of establishing a connection to the remote Postgres instance on ObjectRocket. The article then provided a sample data set and explained how to use the UPDATE statement with WHERE clause to perform various functions for updating specific records, specifically how to update a single table column, how to update multiple columns in a table and then verify the results. Remember the UPDATE function is used to filter the records on an existing table and modify only the rows in the table that meet a specified criteria. Failure to use the WHERE clause will result in all the rows of the table being unintentionally updated.