PostgreSQL Update With Join and Where Clause

Introduction

In PostgreSQL, the UPDATE statement is used to change the value of a column in a table. By using a WHERE clause, you can specify the conditions that dictate which rows get updated. If you omit the WHERE clause from your UPDATE statement, the values for the column will be changed for every row in the table.

The JOIN operator is used to match and combine records from different tables. If you use a JOIN in an UPDATE statement, you can perform a cross-table update– this means that a record in a table will be changed if a value matched records from the second table. In this article, we’ll show you how to perform a PostgreSQL UPDATE with a JOIN and WHERE clause, and we’ll provide plenty of examples to get you started.

Prerequisites

Two key prerequisites need to be in place before proceeding with this tutorial:

• You must have PostgreSQL installed and set up on your machine.
• You must have some basic knowledge of PostgreSQL.

PostgreSQL UPDATE JOIN and WHERE

As we mentioned earlier, a PostgreSQL UPDATE with a JOIN and WHERE clause is the right choice when you want to update the values from your table based on the values from another table. Let’s look at the basic syntax for this type of SQL statement:

If we break down this statement into its individual parts, we can get a better understanding of what’s happening:

• First, we specify the name of the table after the UPDATE keyword.
• We then set the new value of the column that we want to update.
• Using the FROM clause, we specify the joined table.
• Finally, we specify the conditions for the join in the WHERE clause.

Examples

Before we look at any example, we’ll need to create some sample tables and insert records into them. Here’s the SQL statement we’ll use to create our first table:

Let’s insert some records into the first table:

We can then create another table that we’ll use in our UPDATE join SQL statement:

We’ll insert some empty records into this second table to help us verify that the update join statement was successful:

Now that we’ve set up our sample data set, let’s put some utensils into table2. The following statement will update the table2 utensil column based on the values in table1:

We can then use a SELECT SQL statement to display the updated records from the second table:

We can see that the table is now updated with values in the utensil column.

Let’s look at another example. For this example, we’ll update the utensil column to have a value of Dinner Knife in the table table2:

We can use a SELECT statement once again to display the records from the table and check our work:

The records have been updated successfully: Three rows are now changed to have a value of Dinner Knife in the utensil column.

Conclusion

When you’re working with data in PostgreSQL, some of your updates may be a bit complex. You may need to update the values of one table based on values of another table– for these cases, an UPDATE statement that contains a JOIN and a WHERE clause is the best way to get the job done. In this article, we showed you how to perform a PostgreSQL UPDATE with a JOIN and WHERE clause. If you’ve been following along with the examples in this tutorial, you’ll be ready to create effective SQL statements to modify your own PostgreSQL tables.