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:

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 optional WHERE 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 the WHERE 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:

Postgres Partial Update

Execute the following query to perform a partial update on the table in the previous section:

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:

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:

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:

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.