How UPDATE JOIN Works in PostgreSQL?

In PostgreSQL, updating records in one table based on values in another is a common scenario in relational databases. While PostgreSQL does not have a direct UPDATE JOIN syntax like other databases (e.g., MySQL). Hence, it provides a powerful alternative by using the FROM clause in the UPDATE statement. This allows us to update one table based on the conditions and matching rows of another table.

UPDATE JOIN in PostgreSQL

• The UPDATE JOIN in PostgreSQL helps us to update rows in one table using values from another table by joining them. This is useful when you need to update data in one table based on information from a related table.
  
• UPDATE JOIN in PostgreSQL is used to update existing rows in one table by referring to another table based on a JOIN operation. That is extremely helpful in the relational database, especially when the tables are joined based on foreign keys.
  
• PostgreSQL does not support syntax like that of MySQL's UPDATE JOIN. We have to use an UPDATE statement with a subquery including the JOIN clause.

Syntax:

UPDATE table1
SET column1 = new_value
FROM table2
WHERE table1.id = table2.id;

Key Terms:

• table1: The table you want to update.
  
• table2: The table from which the values are joined.
  
• column1: The column in table1 that you want to update.
  
• id: The column used to match rows between table1 and table2.

Examples of PostgreSQL UPDATE JOIN

Let’s take an example where we have two tables. We want to update the customer name in the orders table based on the customer’s id from the customers table.

• orders: Contains order details.
  
• customers: Contains customer information.

1. Customers Table

The query creates a customers table with three columns: id, name, and email, where the id is a primary key. It then inserts three rows of data, adding customer names and email addresses for John Doe, Jane Smith, and Alice Johnson.

Query:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO customers (name, email)  
VALUES  
('John Doe', '[email protected]'),  
('Jane Smith', '[email protected]'),  
('Alice Johnson', '[email protected]');

Output:

2. Orders Table

The query creates an orders table with four columns: order_id (a primary key ), customer_id, customer_name, and customer_email.

It then inserts three rows into the orders table, assigning customer_id values, while leaving the customer_name and customer_email fields as NULL.

Query:

CREATE TABLE orders (     
order_id SERIAL PRIMARY KEY,     
customer_id INT,     
customer_name VARCHAR(100),     
customer_email VARCHAR(100) );

INSERT INTO orders (customer_id, customer_name, customer_email)
VALUES (1, NULL, NULL), (2, NULL, NULL), (3, NULL, NULL);

Output:

Explanation:

• Table Creation: two tables are created namely, customers and orders, with extra columns named email and customer_email where all the email addresses will be stored.
  
• Data Insertion: Here, the data is inserted into the customers table with name and email, orders table with customer_id only; others customer_name and customer_email are initialized with NULL.
  
• Update Query: This UPDATE query takes an INNER JOIN of orders and customers on customer_id=orders.id and id=customers.id. It updates the customer_name and customer_email columns in orders with corresponding values from the customers table:.

Example 1 : Updating Single Column with JOIN

The UPDATE query successfully updates the customer_name in the orders table based on the name from the customers table where the customer_id matches the id in customers.

Query:

UPDATE orders
SET customer_name = customers.name
FROM customers
WHERE orders.customer_id = customers.id;

Output:

Explanation:

• Here, the orders table is updated with the customer_name from the customers table. It uses the FROM clause to reference the customers table.
  
• The orders table is updated where the customer_id in the orders table matches the id in the customers table.
  
• This ensures that the customer_name in the orders table will always reflect the correct name from the customers table when the IDs match.

Example 2: Updating Multiple Columns with JOIN

Now, let’s update both the customer_name and customer_email in the orders table based on data from the customers table.

1. Modify the orders table to include a customer_email field.
   
2. Modify the customers table to include an email field.

1. Adding Email Columns:

ALTER TABLE customers ADD COLUMN email VARCHAR(100);
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(100);

Output:

Explanation:

• ALTER TABLE customers ADD COLUMN email VARCHAR(100); adds a column named email, to the customers table which will store an email address of up to 100 characters.
  
• ALTER TABLE orders ADD COLUMN customer_email VARCHAR(100); adds a column to the orders for customer_email

2. Inserting Data into Orders:

INSERT INTO orders (customer_id, customer_name, customer_email)
VALUES (1, NULL, NULL), (2, NULL, NULL), (3, NULL, NULL); 

Output:

Explanation:

This query inserts three rows into the orders table, setting the customer_id values to 1, 2, and 3, while leaving the customer_name and customer_email fields as NULL for all rows. The order_id is automatically generated because it's set as a SERIAL primary key.

3. Update Query for Multiple Columns:

UPDATE orders
SET customer_name = customers.name,
    customer_email = customers.email
FROM customers
WHERE orders.customer_id = customers.id;

Output:

Explanation:

• This SQL query updates the orders table using customer_name and customer_email, based on data in the customers table by joining the orders table with the customers table based on the customer_id in orders and id in customers.
  
• Where those IDs match, it updates the customer_name with customers.name and the customer_email with customers.email so that both fields in the orders table reflect the correct information about the customer.

Conclusion

In summary, we can update records in one table using values from another related table in PostgreSQL by using the FROM clause in an UPDATE statement. Even though PostgreSQL doesn’t have a direct UPDATE JOIN like some other databases, it can still achieve the same result using SQL joins.

This is useful when we need to keep data in multiple tables in sync or update several columns at once. Understanding how to use UPDATE JOIN is important to ensure our data stays accurate and consistent across related tables.