Turn select into Update with multiple joins Postgres

Question

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?

user330315 · Accepted Answer · 2021-11-30 13:23:06Z

1

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

answered Nov 30, 2021 at 13:23

user330315

Sign up to request clarification or add additional context in comments.

Comments

Grant Fritchey · Accepted Answer · 2021-11-30 13:13:06Z

0

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;

answered Nov 30, 2021 at 13:13

Grant Fritchey

2,84125 silver badges25 bronze badges

1 Comment

user2210516 Over a year ago

Doesn't work :(

Pooya · Accepted Answer · 2021-11-30 18:09:12Z

0

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

Edited

Query for update table4:

update table4 u_t4 set 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 t4.id = u_t4.id and t3.id = 550 and t4.scale is not null and t4.datum is null

edited Nov 30, 2021 at 18:09

answered Nov 30, 2021 at 13:18

Pooya

3,2032 gold badges16 silver badges21 bronze badges

2 Comments

user2210516 Over a year ago

True i made a mistake with the tables. Actually i want to update table4 since the t4.datum is in that table

Pooya Over a year ago

I edited post for update table4

Collectives™ on Stack Overflow

Turn select into Update with multiple joins Postgres

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related