Turn select into Update with multiple joins Postgres-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Reference the alias for the table in the FROM clause and then use that throughout. I think I edited this correctly:

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

CodePudding user response：

I think your problem is because want to update t4.datum from table table1.

You should change set column t4.datum = t1.datum to t1.datum = t4.datum Becuase you want update table1 (Update query: update table1 t1) and t4.datum not reference to table1

Should change query like below (If you want update table1):

update table1 t1 set t1.datum = t4.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