Postgres Update Statement
Introduction
This tutorial will explain how to use the Postgres update statement to update existing information in a table. The update operation is very useful when needing to update some or all of the columns in a specific table record. This tutorial will provide specific examples to help understand how the update operation works and explain the importance of using the WHERE
clause executing using Postgres update statement.
Prerequisite
- PostgreSQL must be properly is installed and configured on the local system in order to use the Postgres update statement.
The Postgres Update Statement
This section will explain how to execute the basic form of the Postgres update statement to show how it enables amending values already stored in a column of a given table.
Following is the basic form of the Postgres update statement:
1 2 3 4 5 | UPDATE <target table> SET col1 = VALUE, col2 = another VALUE ,... WHERE <optional condition>; |
Following is a breakdown of the above update statement:
First, the
UPDATE
clause is specified to declare the named table that the operation will target.Next, specify the columns where the data will be changed, or updated. Alternatively, a value or values may be input via the
SET
clause. As shown in the above example, the command is written to update more than just one column, with the column-value pairs being separated with a comma.
NOTE: Any columns not specified in the query will not be affected by the changes.
- Finally, the
UPDATE
operation can be further defined by the optionalWHERE
clause that can be used to specify a condition the query should satisfy for the update operation to work. However, all the rows in the table will be affected by the changes if theWHERE
clause is omitted in the query.
Postgres Update Statement Example.
The previous section provided an overview on how the Postgres UPDATE statement works. This section will offer some examples to help provide a better understanding of the update operation.
Following is an example showing the tblcars
table from cars
database:
1 2 3 4 5 6 7 8 9 | cars=# SELECT * FROM tblcars; id | brand | carname | remarks ----+--------+-----------+------------------------------ 1 | toyota | fortuner | big engine AND nice body 3 | suzuki | ertiga | small but economical 4 | honda | city | elegant 5 | Ford | Eco Sport | Classy AND great performance 2 | toyota | wigo | cool car (5 ROWS) |
Postgres Partial Update
Execute the following query to perform a partial update on the table in the previous section:
1 2 3 4 | UPDATE tblcars SET remarks = 'nice cars' WHERE brand = 'toyota'; |
Postgres should return the UPDATE 2
prompt that means that two of the rows in the table tblcars
were successfully updated.
To verify the update, query the table again via the SELECT function. The results should resemble the following updated table:
1 2 3 4 5 6 7 8 9 | cars=# select * from cars; id | brand | carname | remarks ----+--------+-----------+------------------------------ 3 | suzuki | ertiga | small but economical 4 | honda | city | elegant 5 | Ford | Eco Sport | Classy and great performance 1 | toyota | fortuner | nice cars 2 | toyota | wigo | nice cars (5 rows) |
Note that the last two rows of the table reflect the changes that were specified for the records that contained a value of toyota
in the brand column.
Postgres Update All Rows
The previous section explained how to perform a partial update. This section will explain how to update all the rows within the same table used in the above example.
First, execute the following query:
1 2 | UPDATE tblcars SET remarks = 'nice cars'; |
Postgres should display a prompt with UPDATE 5
that means all of the rows in the table were affected.
Now execute the SELECT *
operation once more to verify the operation was successful. The results should resemble the following table:
1 2 3 4 5 6 7 8 9 | cars=# SELECT * FROM tblcars; id | brand | carname | remarks ----+--------+-----------+----------- 3 | suzuki | ertiga | nice cars 4 | honda | city | nice cars 5 | Ford | Eco Sport | nice cars 1 | toyota | fortuner | nice cars 2 | toyota | wigo | nice cars (5 rows) |
As expected, all of the records were updated in the column having the same nice cars
values.
Conclusion
This tutorial explained how to use the Postgres update statement to update existing information in a table. The tutorial explained how to execute the basic form of the Postgres update statement, provided an update statement example and gave a breakdown of the code used in the statement. The article also covered how to perform both a partial update and how to update all the rows in the table. The tutorial also covered how to verify the update function for both operations were successful. It is important to remember that the update operation can be more precisely defined by using the optional WHERE
clause to better define a specific condition for the update operation. However, bear in mind that all of the rows in the table will be affected by the changes if the WHERE
clause is omitted in the query.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started