PostgreSQL UPDATE Join: Introduction, Syntax & Example

By /
Update Join In Postgresql

In this tutorial, we will be learning about one of the coolest features of PostgreSQL which is UPDATE join. When you want to update the value of a certain table based on a value in another table, you use the UPDATE join. This is a very interesting topic, so let’s get started without wasting time!

Also Read: PostgreSQL UPDATE: Introduction, Syntax & Examples

Introduction to PostgreSQL UPDATE Join

You can update any value of a table using the WHERE clause. However, did you ever think can we update the value of a table based on a value in another table? We can do this using the UPDATE Join statement.

Note that, the UPDATE join is not a keyword combined. The method using which we update the value of a table based on the value in another table is called the UPDATE join method.

The only difference between the UPDATE and UPDATE join is the FROM clause. The UPDATE join has the FROM clause in its query followed by the WHERE condition.

Basically, you can say, the UPDATE join is the combination of the UPDATE and the SELECT query.

Now let’s see the syntax of the UPDATE join statement and examples as well.

PostgreSQL UPDATE Join Syntax

Following is the syntax of the UPDATE join statement.

Syntax:

UPDATE table1
SET table1.col1=table2.col1
FROM table2
WHERE table1.col=table2.col;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

  • UPDATE table1 – The table name of which you want to update the values
  • SET values – New Values
  • FROM table2 – Another table from which you want to fetch the values and insert them into the first table.
  • WHERE – A condition to update the values

As I said, it is a combination of the UPDATE and SELECT statements. It is the best way to remember the syntax of the UPDATE Join statement.

Now let’s see some examples of the UPDATE join method to clearly understand how it works.

PostgreSQL UPDATE Join Example

Let’s create two tables to demonstrate the UPDATE Join usage.

We will create two tables- employee and salary_bonus_range. The employee table will contain the employees’ information such as name, base salary and salary with bonus. The salary_bonus_range table will contain the salary range and bonus for that salary range.

DROP TABLE IF EXISTS salary_bonus_range;
CREATE TABLE salary_bonus_range (
  id SERIAL PRIMARY KEY,
  lower_limit DECIMAL(10, 2),
  upper_limit DECIMAL(10, 2),
  bonus_amount DECIMAL(10, 2)
);

INSERT INTO salary_bonus_range (lower_limit, upper_limit, bonus_amount)
VALUES (50000.00, 60000.00, 5000.00),
       (60000.01, 70000.00, 7000.00),
       (70000.01, 80000.00, 9000.00);

SELECT * FROM salary_bonus_range;


DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  original_salary DECIMAL(10, 2),
  salary_with_bonus DECIMAL(10, 2),
  bonus_amount DECIMAL(10, 2)
);

INSERT INTO employee (first_name, last_name, original_salary)
VALUES ('John', 'Doe', 55000.00),
       ('Jane', 'Smith', 65000.00),
       ('Michael', 'Johnson', 72000.00),
       ('Emily', 'Williams', 58000.00),
       ('David', 'Brown', 67000.00),
       ('Sophia', 'Jones', 72000.00);


SELECT * FROM employee;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created three columns in the salary_bonus_range- lower limit, upper limit and bonus amount. In the employee table, we have created columns for name, original salary, salary with bonus and bonus amount.

Note that, we didn’t insert any value in the salary with bonus and bonus amount columns in the employee table. That’s because we will use the UPDATE Join statement to update the values in the employee table.

Let’s see these two table records first to check if they are inserted correctly.

Salary Bonus Range Table Data
Salary Bonus Range Table Data
Employee Table Data
Employee Table Data

Perfect! Let’s now write UPDATE join query to update the values in the employee table.

UPDATE employee AS e
SET salary_with_bonus = e.original_salary + s.bonus_amount,
    bonus_amount = s.bonus_amount
FROM salary_bonus_range AS s
WHERE e.original_salary BETWEEN s.lower_limit AND s.upper_limit;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have used the table aliases to make the query shorter. Then we update the values of salary_with_bonus and the bonus_amount column of the employee table. To find the bonus for the particular salary range, we use the WHERE condition.

Now let’s see if the above query updated the values of the employee table successfully.

SELECT * FROM employee;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Update Join Statement
Update Join Statement

Tadaa! As you can see, the above query has modified the values of the employee table.

Note that, to grab the bonus for the particular salary range, the WHERE condition is the most important part. Without it, all records of the employee table will be modified by the first salary bonus only.

Conclusion

In this tutorial, we have learned how can we update the values in a particular table based on values in another table. I hope you have learned something new in this tutorial. If you have, don’t forget to share it with your friends!

Reference

Postgresql official documentation on the UPDATE statement.