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