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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  Thanks guys   Can also do this   -- Doesn t work apparently update vehicles vehicle  set price s price per vehicle from vehicles vehicle v join shipments shipment s on v shipment id s id    But then you ve got the vehicle table in there twice  and you re only allowed to alias it once  and you can t use the alias in the  set  portion

[postgresql] How to do an update + join in PostgreSQL?

Examples related to postgresql

Examples related to syntax