Turn select into Update with multiple joins Postgres

November 30, 2021

So it’s the first time i want to make a update with on a query with multiple joins.

Database = Postgres v.10

This is what i tried so far:

update table1 t1 set t4.datum = t1.datum
from table1 t1
inner join table3 t3 on t3.id = t1.id
inner join table4 t4 on t4.id = t3.t4_id
where t3.id = 550 and t4.scale is not null and t4.datum is null

Error : SQL Error [42712]: ERROR: table name "t1" specified more than once

Next try:

update table1 t1 set t4.datum = t.datum
from table1 t
inner join table3 t3 on t3.id = t.id
inner join table4 t4 on t4.id = t3.t4_id
where t3.id = 550 and t4.scale is not null and t4.datum is null

Error: SQL Error [42703]: ERROR: column "t4" of relation "table1" does not exist
Position: 28

Last try:

update table1 t1 set t4.datum = t.datum
from table1 t
inner join table3 t3 on t3.id = t.id
inner join table4 t4 on t4.id = t3.t4_id
where t1.id = t.id and t3.id = 550 and t4.scale is not null and t4.datum is null

Error: SQL Error [42703]: ERROR: column "t4" of relation "table1" does not exist
Position: 28

What am i doing wrong?

>Solution :

You shouldn’t repeat the target table of an UPDATE in the FROM clause. So something like. The assignment set t4.datum = t.datum also seems wrong. If you want to update table1 you can’t reference t4 on the left hand side of the assignment. Additionally the target columns can’t be "table qualified" inside the SET part (as it’s clear which table’s column is meant)

So I think you are looking for something like this:

update table1 t1 
   set datum = t4.datum
from table3 t3 
  inner join table4 t4 on t4.id = t3.t4_id
where t1.id = t3.id 
  and t3.id = 550 
  and t4.scale is not null 
  and t4.datum is null