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:
| id | first_name | last_name | spouse_name |
| 1 | Harry | West | Angela |
| 2 | Tommy | Mcguire | Stephenie |
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 marital_status, whose 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 false.
| employee_id | is_married |
| 1 | false |
| 2 | false |
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 (employee).
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 SET.
There you go! This is how you do UPDATE with JOIN condition in PostgreSQL.