oracle sql update rows using analytical functions

Question

I have a table with below data

PRODUCT_ID  ORDER_DATE  NEW_ORDER_DATE
1000       2007/09/15   NULL
1000       2007/09/17   Null
1000       2007/09/20   Null
1000       2007/09/23   Null
1000       2007/09/25   Null
1000       2007/09/27   Null

And i have to update new_order_date with data as below

PRODUCT_ID  ORDER_DATE  NEW_ORDER_DATE
1000       2007/09/15   2007/09/16
1000       2007/09/17   2007/09/19
1000       2007/09/20   2007/09/22
1000       2007/09/23   2007/09/24
1000       2007/09/25   2007/09/26
1000       2007/09/27   2100/01/01

As you can see the new_order_date is always computed based on the next row order_date for the same product_id using date - 1 and the last row is updated to a default date 2100/01/01. Could you please let me if there any function that i can use to achieve this

Your previous question title was more succinct. why did you change it? — Kaushik Nayak
– Kaushik Nayak, Commented Apr 17, 2018 at 16:21

Kaushik Nayak · Accepted Answer · 2018-04-17 16:02:30Z

1

Use the LEAD function and a correlated update.

UPDATE product p1
SET NEW_ORDER_DATE = (
        SELECT new_order_date
        FROM (
            SELECT PRODUCT_ID
                ,ORDER_DATE
                ,LEAD(ORDER_DATE, 1, DATE '2100-01-01' + 1) OVER (
                    PARTITION BY PRODUCT_ID ORDER BY ORDER_DATE
                    ) - 1 AS new_order_date
            FROM product
            ) p2
        WHERE p1.PRODUCT_ID = p2.product_id
            AND p1.ORDER_DATE = p2.ORDER_DATE
        );

Demo

answered Apr 17, 2018 at 16:02

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

user1751356 Over a year ago

Thank you Kaushik

Gordon Linoff · Accepted Answer · 2018-04-17 16:33:54Z

0

I would simply do:

update product p
    set new_order_date = (select max(order_date) keep (dense_rank first order by order_date)
                          from product p2
                          where p2.product_id = p.product_id
                         );

This actually keeps the last value as NULL. That is easily fixed using coalesce():

update product p
    set new_order_date = (select coalesce(max(order_date) keep (dense_rank first order by order_date), date '2100-01-01')
                          from product p2
                          where p2.product_id = p.product_id
                         );

answered Apr 17, 2018 at 16:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

oracle sql update rows using analytical functions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related