sql - 如何在PostgreSQL中使用其他2个表引用更新1个表

Question

我有两张桌子。order_product_table和Transfer_product_table
我想更新order_product_table分支。如果您看到inventory_product_table branch id，我希望在branch 2 order_product_table中更新100510产品，如您在result中看到的那样
订单产品表

id    | date         | Product_id  | value 
-------------------------------------------
1     | 2017-07-01   | 1           | 10
2     | 2017-08-05   | 2           | 20
3     | 2017-10-02   | 3           | 31
4     | 2018-01-20   | 4           | 0

转移产品表

id    | date         | Product_id  | value 
--------------------------------------------
1     | 2017-08-01   | 1           | 10
2     | 2017-10-06   | 2           | 20
3     | 2017-12-12   | 2           | 31
4     | 2018-06-25   | 5           | 5

库存产品表

id    | ref#   | branch_id       
--------------------------- 
1     |100510  |   1    
2     |200511  |   1      
3     |302522  |   1     
4     |100510  |   2

最终（订单产品表）

id    | date         | Product_id  | value 
-------------------------------------------
1     | 2017-07-01   | 1           | 10
2     | 2017-08-05   | 2           | 20
3     | 2017-10-02   | 3           | 31
4     | 2018-01-20   | 4           | 5

Answer 1

Update order_product_table iop
SET value = sub.value 
FROM (SELECT product_id, cost from Transfer_product_table) sub 
inner join Inventory_product_table ip on sub.product_id = ip.id
WHERE ip.branch_id = '2'
AND sub.product_id = ip.id