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           | 20
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

UPDATE Transfer_product_table imp
    SET    value = sub.value
    FROM  (SELECT product_id,value
    ,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY orderdate DESC)AS Rno
    FROM order_product_table
    where orderdate between '2017-07-01' and '2019-10-31') sub

WHERE  imp.product_id = sub.product_id
and  sub.Rno=1
and imp.date between '2017-07-01' and '2019-10-31'