mysql – How to get a record by date from 1 table and update another table in Postgresql?

I have two tables. In a table (order_produt) have multiple records per
The date and another table (Transfer Product) are also multiple records by date.
The order_product table has the correct record. I want to update my
Product transfer table with product order table by date range.

order_product_table
-------------------------

id | date | Product_id | value
-------------------------------------------
1 | 2017-07-01 | 2 | 53
2 | 2017-08-05 | 2 | 67
3 | 2017-10-02 | 2 | 83
4 | 2018-01-20 | 5 | 32
5 | 2018-05-01 | 5 | 53
6 | 2008-08-05 | 6 | 67


Transfer_product_table
----------------------------

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

Result (Transfer_product_table)
--------------------------------
id | date | Product_id | value
--------------------------------------------
1 | 2017-08-01 | 2 | 53
2 | 2017-10-06 | 2 | 83
3 | 2017-12-12 | 2 | 83
4 | 2018-06-25 | 5 | 53

I want a value update by date as you can see in the results table.

I use the query partition, but this is not what I want.

                UPDATE Transfer_product_table imp
SET value = sub.value
FROM (SELECT product_ID, value
, ROW_NUMBER () OVER (PARTITION BY product_id ORDERED by orderdate DESC) AS Rno
FROM order_product_table
where you order between & # 39; 2017-07-01 & # 39; and & # 39; 2019-10-31 & # 39;) sub

WHERE imp.product_id = sub.product_id
and sub.Rno = 1
and impdate between & # 39; 2017-07-01 & # 39; and & # 39; 2019-10-31 & # 39;