How to make update query using data from two another tables (postgresql)

Question

I have 3 tables:

manager with columns:

id serial PK

money int

facilities_work_data with columns:

id serial PK

income integer

manager_facilities with colunns:

id serial PK

manager_id references manager(id)

facilities_work_data_id references facilities_work_data(id)

The aim is to update all manager table rows by adding to manager.money column the value from facilities_work_data.income which in turn has to have id column selected as:

SELECT facilities_work_data_id  from manager_facilities WHERE manager.id = manager_facilities.manager_id

At least I found the following request:

UPDATE A AS a SET money = a.money + b.income FROM B AS b WHERE b.a_id = a.id

But its not my case, seems like I need one more join.

Could you help with such a request?

radar · Accepted Answer · 2014-09-29 17:30:43Z

1

you can get data from two tables and add the conditions in the where clause like below

update manager M
set money = money + FWD.income
FROM manager_facilities MF,  facilities_work_data FWD
WHERE  M.id = MF.manager_id
AND MF.facilities_work_data_id = FWD.id

answered Sep 29, 2014 at 17:30

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

How to make update query using data from two another tables (postgresql)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related