How to do an update + join in PostgreSQL?

Question

Basically, I want to do this:

update vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id=s.id 
set v.price=s.price_per_vehicle;

I'm pretty sure that would work in MySQL (my background), but it doesn't seem to work in postgres. The error I get is:

ERROR:  syntax error at or near "join"
LINE 1: update vehicles_vehicle v join shipments_shipment s on v.shi...
                                  ^

Surely there's an easy way to do this, but I can't find the proper syntax. So, how would I write this In PostgreSQL?

Answer 1

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)

Answer 2

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
└────────────┴─────────┴───────┘

Answer 3

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;

Answer 4

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.

Answer 5

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

Answer 6

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.

Answer 7

The UPDATE syntax is:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In your case I think you want this:

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

Or if you need to join on two or more tables:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

Answer 8

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; $$;

Answer 9

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

Answer 10

--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) 

Answer 11

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.

Answer 12

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.

Answer 13

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';

Answer 14

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/

Answer 15

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

Answer 16

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.

Answer 17

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).

Answer 18

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;