PostgreSQL database is used to create multiple tables to store data on it and access it when needed using queries. These queries can be used to fetch data from multiple tables using JOINS and create other tables to display on the screen. JOINS are very useful in the database to gain useful information through them. Once a join is created in PostgreSQL, it can be modified using the UPDATE JOIN statement.

This guide will explain the use of UPDATE JOIN in Postgres using syntax and examples.

How to Use UPDATE JOIN in Postgres?

The UPDATE JOIN is used to update or change the data in the table with reference to the values of the other tables. It combines two or more tables containing at least one similar field to change the value of the first table on the values of the other table.

Syntax

The following is the syntax of the UPDATE JOIN in PostgreSQL:

UPDATE TableA
   SET TableA.c1 = New_Value
   FROM TableB
   WHERE TableA.c2 = TableB.c2;

The above syntax:

- It begins with the UPDATE keyword and the table name.
- Use the SET keyword to assign the value of the column of the above table.
- In the FROM clause, specify/write the name of the reference table.
- The WHERE keyword is used to provide the reference column from both tables.

Example: UPDATE JOIN in PostgreSQL

The following example will explain the use of the UPDATE JOIN in PostgreSQL. First, type the following query to get the values of the “customers” table:

SELECT * FROM customers;

Running the above query will display all the data from the “customers” table:

Use the following query to get the data of the orders table:

SELECT * FROM orders;

Now use the UPDATE JOIN statement on the given tables:

UPDATE customers
 SET email = 'newemail@example.com'
 FROM orders
 WHERE orders.customer_id = customers.id
  AND orders.price > 10;

The above code will update the email column of the “customers” table by assigning them a new value. It will take the reference from the orders table and use the “customer_id” column from both and also check the price to change the email:

Use the following query to get the updated values of the table:

SELECT * FROM customers;

Running the above code has displayed the changed value of the email column:

That’s all about using UPDATE JOIN in PostgreSQL.

Conclusion

PostgreSQL tables are linked through relationships, and data is stored in these tables that can be accessed at any time. JOINS are used to combine multiple tables of the database and apply queries on them to fetch useful information through them. UPDATE JOIN is used to alter the values of the first table using the reference column from the other tables. This guide has demonstrated the use of UPDATE JOIN in PostgreSQL with examples.