Postgresql Update with Join

How to do an update + join in PostgreSQL?

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 

Postgresql Update with join

General Update Syntax:

[ 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 ] [, ...] ]

Solution for you problem:

UPDATE customers AS c
SET cutoffstop = cutoffstop + 432000
FROM bluemedia as b
WHERE c.id = b.customerid
AND b.orderid = '217201807'

For more information on UPDATE syntax follow the below link:

https://www.postgresql.org/docs/current/static/sql-update.html

UPDATE statement with multiple joins to main table in PostgreSQL

As documented in the manual you should not repeat the target table in the FROM clause of an UPDATE statement.

The FROM clause for an UPDATE unfortunately doesn't follow the exact same rules as the one used in the SELECT clause. It's easier to use the old implicit joins instead of the (usually preferred) explicit JOIN operators.

As far as I can tell this is what you are looking for:

UPDATE eutmpdfhdt as t
  SET nwcolid = t2.nwcolid
FROM eutmpdfh t1, 
     eutmptbldt t2 
WHERE t.dfhid = t1.dfhid 
  AND t.colid = t2.colid  
  AND t1.dfhtyp IN ('D','U','S','P','B')

UPDATE statement with multiple joins in PostgreSQL

The same as valid UPDATE statement in Postgres:

UPDATE incode_warrants iw
SET    warn_docket_no = iv.viol_docket_no
FROM   incode_warrantvs  iwvs
JOIN   incode_violations iv ON iv.viol_citation_no = iwvs.warnv_citation_no
                           AND iv.viol_viol_no = iwvs.warnv_viol_no
WHERE  iw.warn_rid = iwvs.warnv_rid;
-- AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no -- see below

You cannot just use a table alias in the FROM clause as target table in the UPDATE clause. The (one!) table to be updated comes right after UPDATE keyword (if we ignore a possible ONLY keyword in between). You can add an alias there if you want. That's the immediate cause of your error message, but there's more.

The column to be updated is always from the one table to be updated and cannot be table-qualified.

You don't need to repeat the target table in the FROM clause - except for special cases like this:

• PostgreSQL: update with left outer self join ignored

This optional addition can avoid pointless cost by suppressing updates that do not change anything:

AND iw.warn_docket_no IS DISTINCT FROM iv.viol_docket_no

See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

More in the excellent manual on UPDATE.

How to UPDATE a column from a self join table in PostgreSQL?

You were using the Microsoft-Syntax for UPDATE.
(and you don't need the COALESCE(), since a.propertyaddress is always NULL)

Postgres documentation for UPDATE

---

UPDATE Housing a
SET propertyaddress = b.propertyaddress
FROM Housing b
WHERE a.ParcelID = b.ParcelID
  AND a.uniqueid <> b.uniqueid
  AND a.propertyaddress IS NULL
  ; 

How to fix postgresql update with join performance?

Don't include ways in the from clause! This doesn't do what you expect. Presumably, you want:

update ways w
    set end_node_id = n.node_id
from nodes n
where st_endpoint(w.shape) = n.shape;

In your formulation, the ways in the update is a different reference from the ways in the from. So, your code is creating Cartesian product -- which no doubt slows down the processing. Note that this is different from the behavior of SQL Server, which has similar syntax.

postgres update with join slow performance

This happens because of the indexes. Every update in postgres is considered as reinsertion of that row regardless of the column getting updated, so all indexes are recalculated. To make it faster, dropping indexes or swapping to a new table would work (if it is possible to do those).

---

Related Topics