PostgreSQL UPDATE ... FROM

Sometimes, you need to update rows in one table based on those rows in another table. For example, update the sales statistics table according to the product sales details table.

In PostgreSQL, you can do this with subqueries, or with UPDATE...FROM statements conveniently.

PostgreSQL UPDATE…FROM syntax

To update rows in one table based on rows in another table, please use a PostgreSQL UPDATE...FROM statement following this syntax:

UPDATE [IGNORE] table_name
SET
    column_name1 = value1,
    column_name2 = value2,
    ...
FROM another_table[, ...]
WHERE clause
[RETURNING expr];

Explanation,

• Compared with the ordinary UPDATE statement, this statement has a more FROM clause, and the WHERE clause is required.
• You need to specify the join conditionsfor the two tables in the WHERE clause.

For example the following statement:

UPDATE a
SET a.c1 = b.c2
FROM b
WHERE a.b_id = b.id;

For each row of the a table, the UPDATE statement checks each row of the b table. If the value of the b_id of the b is equal to value of the b_id of a, the UPDATE statement will set the value of the c1 column of a to the value of the c2 column of b.

PostgreSQL UPDATE…FROM Examples

Below we will use a few examples to demonstrate the specific usage of UPDATE…FROM.

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

The examples here use the following tables:

• The cities information is stored in the city table.
• The countries information is stored in the country table.

Suppose, there is a requirement to append @ and country name to the city names in the city table, please run the following statement:

UPDATE city_copy a
SET city = city || '@' || b.country
FROM country b
WHERE b.country_id = 1 or   b.country_id = 2
RETURNING city_id, city;

 city_id |                       city
---------+--------------------------------------------------
       1 | A Corua (La Corua)@Spain
       2 | Abha@Saudi Arabia
       3 | Abu Dhabi@United Arab Emirates
       4 | Acua@Mexico
       5 | Adana@Turkey
       6 | Addis Abeba@Ethiopia
       7 | Aden@Yemen
       8 | Adoni@India
       9 | Ahmadnagar@India
      10 | Akishima@Japan
      ...
(600 rows)

Here, we use the clause FROM country b in the UPDATE statement to get rows from the country table that meet the condition (a.country_id = b.country_id) and apply the value of the country column to the expression (city || '@' || b.country ).

You can also use a subquery to achieve the above requirements, as follows:

UPDATE city_copy a
SET city = (
  SELECT a.city || '@' || b.country
  FROM country b
    WHERE a.country_id = b.country_id
)
RETURNING city_id, city;

Conclusion

PostgreSQL UPDATE...FROM statements allow you to update rows in a table based on rows ​​in another table.