Postgres update left join

by

The Postgres UPDATE command can be used in combination with a LEFT JOIN to update specific columns in a table using values from another table.

Here’s an example to illustrate how it works:


UPDATE table1
SET column1 = table2.columnA,
    column2 = table2.columnB
FROM table2
WHERE table1.id = table2.id;

In this example, we have two tables: table1 and table2. We want to update column1 and column2 in table1 with the corresponding values from table2 based on a matching id column.

The SET clause specifies the columns to be updated in table1 and their new values. In this case, we are updating column1 with the value of columnA from table2, and column2 with the value of columnB from table2.

The FROM clause specifies the table from which we want to retrieve the values to update table1. In this case, it’s table2.

The WHERE clause is used to specify the condition for the join. It ensures that only rows with matching id values are updated.

Here’s a sample scenario to better understand how this works.

Let’s assume we have the following data in our tables:

table1 table2
id column1 column2
1 old_value1 old_value2
2 old_value3 old_value4
id columnA columnB
1 new_value1 new_value2
3 new_value3 new_value4

After executing the above UPDATE query, table1 will be updated as follows:

id column1 column2
1 new_value1 new_value2
2 old_value3 old_value4

As you can see, only the row with id = 1 was updated because it had a matching id in both table1 and table2.

Leave a comment