UPDATE statement with JOIN in PostgreSQL
Most of the time when we update a column in a table, it is based on some other column in the same column.
Let's say we have a table
employee as shown below:
If we need to update the
spouse_name of the row with
id = 1 to Rebecca, we do it in this manner:
UPDATE employee set spouse_name = 'Rebecca' WHERE id = 1;
So, far so good.
Let's say we have a second table called
is_married column is to be set based on the
spouse_name column in
employee table. And, we set it to
true if the
spouse_name field is present in the
employee table, and if not, set it to
This update is different from the first one because here we need to update a field (
is_married) of a table (
marital_status) based on a field (
spouse_name) of some other table (
Here, it is quite obvious that we need to perform a
join operation. But, what is the exact PostgreSQL syntax for this?
The syntax goes like this.
UPDATE marital_status ms SET is_married = true FROM employee e WHERE ms.employee_id = e.id;
So, we specify the joined table after the
FROM clause and provide the join condition on the
WHERE clause. And, the
FROM clause is immediately preceded by
There you go! This is how you do
JOIN condition in PostgreSQL.