How to do an update + join in PostgreSQL?

The answer of Mark Byers is the optimal in this situation. Though in more complex situations you can take the select query that returns rowids and calculated values and attach it to the update query like this:

with t as ( -- Any generic query which returns rowid and corresponding calculated values select t1.id as rowid, f(t2, t2) as calculatedvalue from table1 as t1 join table2 as t2 on t2.referenceid = t1.id ) update table1 set value = t.calculatedvalue from t where id = t.rowid 

This approach lets you develop and test your select query and in two steps convert it to the update query.

So in your case the result query will be:

with t as ( select v.id as rowid, s.price_per_vehicle as calculatedvalue from vehicles_vehicle v join shipments_shipment s on v.shipment_id = s.id ) update vehicles_vehicle set price = t.calculatedvalue from t where id = t.rowid 

Note that column aliases are mandatory otherwise PostgreSQL will complain about the ambiguity of the column names.

Let me explain a little more by my example.

Task: correct info, where abiturients (students about to leave secondary school) have submitted applications to university earlier, than they got school certificates (yes, they got certificates earlier, than they were issued (by certificate date specified). So, we will increase application submit date to fit certificate issue date.

Thus. next MySQL-like statement:

UPDATE applications a JOIN ( SELECT ap.id, ab.certificate_issued_at FROM abiturients ab JOIN applications ap ON ab.id = ap.abiturient_id WHERE ap.documents_taken_at::date < ab.certificate_issued_at ) b ON a.id = b.id SET a.documents_taken_at = b.certificate_issued_at; 

Becomes PostgreSQL-like in such a way

UPDATE applications a SET documents_taken_at = b.certificate_issued_at -- we can reference joined table here FROM abiturients b -- joined table WHERE a.abiturient_id = b.id AND -- JOIN ON clause a.documents_taken_at::date < b.certificate_issued_at -- Subquery WHERE 

As you can see, original subquery JOIN's ON clause have become one of WHERE conditions, which is conjucted by AND with others, which have been moved from subquery with no changes. And there is no more need to JOIN table with itself (as it was in subquery).

For those actually wanting to do a JOIN you can also use:

UPDATE a SET price = b_alias.unit_price FROM a AS a_alias LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id WHERE a_alias.unit_name LIKE 'some_value' AND a.id = a_alias.id; 

You can use the a_alias in the SET section on the right of the equals sign if needed. The fields on the left of the equals sign don't require a table reference as they are deemed to be from the original "a" table.

For those wanting to do a JOIN that updates ONLY the rows your join returns use:

UPDATE a SET price = b_alias.unit_price FROM a AS a_alias LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id WHERE a_alias.unit_name LIKE 'some_value' AND a.id = a_alias.id --the below line is critical for updating ONLY joined rows AND a.pk_id = a_alias.pk_id; 

This was mentioned above but only through a comment..Since it's critical to getting the correct result posting NEW answer that Works

Here we go:

UPDATE vehicles_vehicle v SET price = s.price_per_vehicle FROM shipments_shipment s WHERE v.shipment_id = s.id; 

Simple as I could make it.

WORKS PERFECT!!!

POSTGRE SQL - UPDATE With a JOIN

BELOW CODE - Check the positioning of columns and IDs as below:

If you place it exactly as below, then only it will work!

---IF you want to update This table1 using table2 UPDATE table1 SET attribute1 = table2.attribute2 FROM table2 WHERE table2.product_ID = table1.product_ID; 

To add something quite important to all the great answers above, when you want to update a join-table, you may have 2 problems:

• you cannot use the table you want to update to JOIN another one
• Postgres wants a ON clause after the JOIN so you cannot only use where clauses.

This means that basically, the following queries are not valid:

UPDATE join_a_b SET count = 10 FROM a JOIN b on b.id = join_a_b.b_id -- Not valid since join_a_b is used here WHERE a.id = join_a_b.a_id AND a.name = 'A' AND b.name = 'B' 

UPDATE join_a_b SET count = 10 FROM a JOIN b -- Not valid since there is no ON clause WHERE a.id = join_a_b.a_id AND b.id = join_a_b.b_id a.name = 'A' AND b.name = 'B' 

Instead, you must use all the tables in the FROM clause like this:

UPDATE join_a_b SET count = 10 FROM a, b WHERE a.id = join_a_b.a_id AND b.id = join_a_b.b_id AND a.name = 'A' AND b.name = 'B' 

It might be straightforward for some but I got stuck on this problem wondering what's going on so hopefully, it will help others.

Here's a simple SQL that updates Mid_Name on the Name3 table using the Middle_Name field from Name:

update name3 set mid_name = name.middle_name from name where name3.person_id = name.person_id; 

The link below has a example that resolve and helps understant better how use update and join with postgres.

UPDATE product SET net_price = price - price * discount FROM product_segment WHERE product.segment_id = product_segment.id; 

See: http://www.postgresqltutorial.com/postgresql-update-join/

First Table Name: tbl_table1 (tab1). Second Table Name: tbl_table2 (tab2).

Set the tbl_table1's ac_status column to "INACTIVE"

update common.tbl_table1 as tab1 set ac_status= 'INACTIVE' --tbl_table1's "ac_status" from common.tbl_table2 as tab2 where tab1.ref_id= '1111111' and tab2.rel_type= 'CUSTOMER'; 

Some care should be taken if the join is performed on a non-unique column. I.e. the result of the join produces more values that can be used in the update.

Some RDMS raise an exception is this case, but PostgreSQL apparently performs the update with a non deterministic outcome.

Example

Tested on 14.1

create table tab as select * from (values (1,'a'), (2,'b') ) t(id, att); 

We use a CTE where the id = 1 id giving two possible values for the update. Using the order by in the CTE we get a different results.

with t as ( select * from (values (1,'c'), (1,'d') ) t(id, att) order by 2 /* Based on this order different update is performed */ ) update tab set att = t.att from t where tab.id = t.id 

With the ascendig order the column is updated to the value of d (highest value)

id|att| --+---+ 1|d | 2|b | 

while using a descending order in the CTE the column is updated to the value of c (lowest value)

id|att| --+---+ 1|c | 2|b | 

The moral of the story always check if the join produce a unique result.

The relevant part of the documentation

When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

To UPDATE one Table using another, in PostGRE SQL / AWS (SQL workbench).

In PostGRE SQL, this is how you need to use joins in UPDATE Query:

UPDATE TABLEA set COLUMN_FROM_TABLEA = COLUMN_FROM_TABLEB FROM TABLEA,TABLEB WHERE FILTER_FROM_TABLEA = FILTER_FROM_TABLEB; Example: 

Update Employees Set Date_Of_Exit = Exit_Date_Recorded , Exit_Flg = 1 From Employees, Employee_Exit_Clearance Where Emp_ID = Exit_Emp_ID 

Table A - Employees Columns in Table A - Date_Of_Exit,Emp_ID,Exit_Flg Table B is - Employee_Exit_Clearance Columns in Table B - Exit_Date_Recorded,Exit_Emp_ID

1760 rows affected

Execution time: 29.18s

--goal: update selected columns with join (postgres)--

UPDATE table1 t1 SET column1 = 'data' FROM table1 RIGHT JOIN table2 ON table2.id = table1.id WHERE t1.id IN (SELECT table2.id FROM table2 WHERE table2.column2 = 12345) 

In case you don't have the value in one column but instead had to calculate it from the other table (in this example price_per_vehicle from shipments_shipment). Then assuming that shipments_shipment has price and vehicle_id columns the update for a specific vehicle could look like this:

-- Specific vehicle in this example is with id = 5 WITH prices AS ( SELECT SUM(COALESCE(s.price, 0)) AS price_per_vehicle FROM shipments_shipment AS s WHERE s.vehicle_id = 5 ) UPDATE vehicles_vehicle AS v SET v.price = prices.price_per_vehicle FROM prices WHERE v.id = 5 

EDIT: do not use, execution time increases quadratically

It's a pity that the runtime is so bad, because the syntax was very elegant. I'm leaving this answer up to save others from going down this path.

This answer is different from the rest because you don't have to repeat the join condition.

• You join once in the FROM clause
• and the WHERE clause checks that rows from X are present in (X as X_joined join Y).

As a result this works with natural joins, which is very nice.

Example query

Say that you have a table shipment that you want to augment with information from table vehicle, and both tables have a column vehicle_id so you can use NATURAL JOIN.

---- DO NOT USE, quadratic runtime ---- EXPLAIN UPDATE shipment SET shipment.speed = vehicle.average_speed FROM shipment s_joined NATURAL JOIN vehicle WHERE -- This is the magic condition -- (EDIT: ... it probably causes the quadratic runtime, too) shipment = s_joined -- any further limitations go here: AND shipment.destination = 'Perth' 

Minimal working example

-- A table with shipments, some with missing speeds create temporary table shipment ( vehicle_id varchar(20), cargo varchar(20), speed integer ); insert into shipment values ('cart', 'flowers', 60), ('boat', 'cabbage', null), ('cart', 'potatos', null), ('foot', 'carrots', null); -- A table with vehicles whose average speed we know about create temporary table vehicle ( vehicle_id varchar(20), average_speed integer ); insert into vehicle values ('cart', 6), ('foot', 5); -- If the shipment has vehicle info, update its speed ---- DO NOT USE, quadratic runtime ---- UPDATE shipment SET speed = vehicle.average_speed FROM shipment as s_joined natural join vehicle WHERE shipment = s_joined AND shipment.speed is null; -- After: TABLE shipment; ┌────────────┬─────────┬───────┐ │ vehicle_id │ cargo │ speed │ ├────────────┼─────────┼───────┤ │ cart │ flowers │ 60 │ <- not updated: speed was not null │ boat │ cabbage │ │ <- not updated: no boat in join │ cart │ potatos │ 6 │ <- updated │ foot │ carrots │ 5 │ <- updated └────────────┴─────────┴───────┘ 

If you want to check whether your joins are proper or not you can run your update query in transaction, using this you can run select query on original table which gives you exactly those results which you are looking for, and after confirmation you can finalize the query.

BEGIN; -- Begin the transaction UPDATE schema.village vill SET wst_id = vill_view.wst_id_023::INT, --if you want to cast the column type wst_dist = vill_view.wst_dist_023::numeric --if you want to cast the column type FROM schema.village_view vill_view WHERE vill.id = vill_view.id::double precision; --if you want to cast the column type -- Verify the result SELECT * FROM schema.village -- ROLLBACK; -- if your query fails or fills that something is wrong you can reverse the all updates and table will remain as original -- COMMIT -- if you fills all are good (no return to original table once commited) 

The first way is slower than the second way.

First:

DO $$ DECLARE page int := 10000; min_id bigint; max_id bigint; BEGIN SELECT max(id),min(id) INTO max_id,min_id FROM opportunities; FOR j IN min_id..max_id BY page LOOP UPDATE opportunities SET sec_type = 'Unsec' FROM opportunities AS opp INNER JOIN accounts AS acc ON opp.account_id = acc.id WHERE acc.borrower = true AND opp.sec_type IS NULL AND opp.id >= j AND opp.id < j+page; COMMIT; END LOOP; END; $$; 

Second:

DO $$ DECLARE page int := 10000; min_id bigint; max_id bigint; BEGIN SELECT max(id),min(id) INTO max_id,min_id FROM opportunities; FOR j IN min_id..max_id BY page LOOP UPDATE opportunities AS opp SET sec_type = 'Unsec' FROM accounts AS acc WHERE opp.account_id = acc.id AND opp.sec_type IS NULL AND acc.borrower = true AND opp.id >= j AND opp.id < j+page; COMMIT; END LOOP; END; $$;