Update Statement with Multiple Joins in Postgresql

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.

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

PostgreSQL update column with JOIN over 3 tables

In Postgres, you can express joins in the update statement:

update table1 t1 
   set ?? = ??
   from table_con tc join
        table2 t2 
        on t2.x_id = tc.x_id
   where t1.p_id = tc.p_id;

Fill in the set column with the column and value you wnt to set.

Postgres UPDATE using SELECT whit INNER JOIN multiple tables

Postgres allows you to do updates in CTEs. Perhaps this does what you want:

with data as (
      select t1.id, t2.id_t2, t2.icon, t3.id_t3, t3.title,
             t4.id_t4, t4.description
      from table1 t1 join
           table5 t5 
           on id_t5 = id_t3 join
           table3 
           on id_t1 = id_t3 and id_t3 = 816 left join
           table2 t2
           on t5.id_t2_fk = t2.id_t2 left join
           table4 t4
           on t4.id_t3_fk = t1.id_t1
       where t1.id_t1= 816
      ),
      u2 as (
       update table2
           set icon = 'new icon'
           where t2.id_t3 in (select data.id_t2 from data)
       returning *
      ),
      u3 as (
       update table3
           set title = 'new title'
           where id_t3 in (select data.id_t3 from data)
       returning *
      )
update table4
   set description = 'new description'
   where id_t4 in (select data.id_t4 from data);

If not, something similar will.

Postgres update with an inner join across 3 tables?

The below SQL should do what you are asking:

UPDATE animal_attrib_values aav
SET animal_attrib_value_name= 'true'
WHERE aav.animal_attrib_value_id = (
    SELECT a.animal_attrib_value_id 
    FROM animals a 
    WHERE a.animal_id = 458 
        AND a.animal_attrib_type_id  = 38
)
;

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 

“Not exists” across multiple joins

Your query probably returns no values because you join table4 on contact_id and then you exclude in the WHERE clause the rows which come from this join.

To find values that don't exist, you can usually use LEFT JOIN or RIGHT JOIN or FULL OUTER JOIN and then filter the rows with NULL values in the WHERE clause.

Try this :

SELECT t1.id
FROM table1 t1
LEFT JOIN table2 t2 using(id)
LEFT JOIN table3 t3 using(id)
LEFT JOIN table4 t4 using(contact_id)
WHERE t4.contact_id IS NULL

---

Related Topics