How to use PostgreSQL UPDATE WHERE on an ObjectRocket Instance

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

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:

Screenshot

How to Connect to PostgreSQL Instance

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

1
2
3
4
5
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [PostgreSQL]: <your_database_name>
Port [5432]: 4149
Username [PostgreSQL]: pguser
Password FOR USER orkb: <your_password>

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

1
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149

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:

1
2
3
UPDATE <table_name>
SET <a_column OR some_column>
WHERE <condition>

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |    item_name    | price | quantity
----+-----------------+-------+----------
  1 | yogurt          | $5.00 |       10
  2 | lettuce         | $5.00 |       10
  3 | sardines        | $3.00 |       20
  4 | ham             | $6.00 |       30
  5 | bacon           | $7.00 |       20
  6 | chicken nuggets | $6.00 |       20
  7 | coke in can     | $7.00 |       20
  8 | oreo            | $4.00 |       20
  9 | cheese          | $5.00 |       20
 10 | bread           | $8.00 |       25
(10 rows)

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:

1
2
3
UPDATE grocery_list
SET item_name = 'bottle of coke'
WHERE id = 7;

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

Now verify the result using the following SELECT statement:

1
2
3
4
5
sandbox=> SELECT * FROM grocery_list WHERE id = 7;
 id |   item_name    | price | quantity
----+----------------+-------+----------
  7 | bottle OF coke | $7.00 |       20
(1 ROW)

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):

1
2
3
UPDATE grocery_list
SET price = '$8', quantity = 12
WHERE quantity = 10;

The result should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sandbox=> SELECT * FROM grocery_list;
 id |    item_name    | price | quantity
----+-----------------+-------+----------
  3 | sardines        | $3.00 |       20
  4 | ham             | $6.00 |       30
  5 | bacon           | $7.00 |       20
  6 | chicken nuggets | $6.00 |       20
  8 | oreo            | $4.00 |       20
  9 | cheese          | $5.00 |       20
 10 | bread           | $8.00 |       25
  7 | bottle OF coke  | $7.00 |       20
  1 | yogurt          | $8    |       12
  2 | lettuce         | $8    |       12
(10 ROWS)

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.

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.