0

I have two tables. In one table (order_product) I have multiple records by date and in the other table (Transfer_product) also multiple record by date. order_product table have correct record. I want to update transfer_product table with order_product 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           | 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

I want by date value update like you can see Result table.

I used window functions 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 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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.