PostgreSQL Update With Join and Where Clause

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

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:

1
2
3
4
UPDATE TABLE_NAME
SET TABLE_NAME.column_name = VALUE
FROM table_name2
WHERE TABLE_NAME.column = table_name2;

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:

1
2
3
4
CREATE TABLE table1 (
id INTEGER PRIMARY KEY,
utensil VARCHAR NOT NULL
);

Let’s insert some records into the first table:

1
2
3
4
5
6
INSERT INTO table1 (id, utensil)
VALUES ('1', 'Spoon'),
('2', 'Fork'),
('3', 'Bread Knife'),
('4', 'Plate'),
('5', 'Water Glass');

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

1
2
3
4
CREATE TABLE table2 (
id INTEGER PRIMARY KEY,
utensil VARCHAR NOT NULL
);

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

1
2
3
4
5
6
INSERT INTO table2 (id, utensil)
VALUES ('1', ''),
('2', ''),
('3', ''),
('4', ''),
('5', '');

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:

1
2
3
4
UPDATE table2
SET utensil = table1.utensil
FROM table1
WHERE table2.id = table1.id;

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

1
2
3
4
5
6
7
8
9
SELECT * FROM table2;
 id |   utensil
----+-------------
  1 | Spoon
  2 | Fork
  3 | Bread Knife
  4 | Plate
  5 | Water Glass
(5 ROWS)

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:

1
2
3
4
UPDATE table2
SET utensil = 'Dinner Knife'
FROM table1
WHERE table2.id = table1.id - 2;

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

1
2
3
4
5
6
7
8
9
SELECT * FROM table2;
  id |   utensil
-----+---------------
   1 | Dinner Knife
   2 | Dinner Knife
   3 | Dinner Knife
   4 | Plate
   5 | Water Glass
(5 ROWS)

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.

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.