PostgreSQL update column with JOIN over 3 tables

Question

I have 3 tables and I want to update table1 (column status) with information (column status) from table2. the link between table1 and table2 is in the table table_con

http://www.sqlfiddle.com/#!15/6ce460/4

I thought about a join and using the result of the join to update table1

select t1.status as t1status,t2.status as t2status,t1.p_id as t1pid, t2.x_id as t2xid
   from table1 t1
      JOIN table_con tc
      ON t1.p_id = tc.p_id

      JOIN table2 t2 
      ON t2.x_id = tc.x_id;

The join works so far, but I don't know how to continue, and the query should work in psql. thanks

What columns do you want to update?

Gordon Linoff
– Gordon Linoff

2016-04-06 22:05:21 +00:00
Commented Apr 6, 2016 at 22:05 — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2016 at 22:05

Kamil Gosciminski · Accepted Answer · 2016-04-06 22:29:33Z

7

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.

edited Apr 6, 2016 at 22:29

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

answered Apr 6, 2016 at 22:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

A MJ Over a year ago

What if t1 has to be joined with both tc and t2?

Gordon Linoff Over a year ago

@AMJ . . . Then you would add another condition in the WHERE clause.

Collectives™ on Stack Overflow

PostgreSQL update column with JOIN over 3 tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related