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