How do I update particular record in below scenario?

Question

I have a table data structure like below,

ID	NAME	START_DATE	END_DATE	CREATE_DATE
100	Apple	01-NOV-23	31-DEC-99	01-NOV-23
100	Anar	10-OCT-23	31-DEC-99	12-SEP-23
100	Anar	04-SEP-23	10-OCT-23	04-SEP-23
101	Ant	01-OCT-23	31-DEC-99	01-OCT-23
101	Bee	10-APR-23	31-DEC-99	12-APR-23
101	Bee	04-MAR-23	10-APR-23	04-MAR-23

I want to update data like below, (2nd row start_date should update like CREATE_DATE and END_DATE should update from next start_date)

ID	NAME	START_DATE	END_DATE	CREATE_DATE
100	Apple	01-NOV-2023	31-DEC-99	01-NOV-2023
**100	Anar	12-SEP-2023	01-NOV-2023	12-SEP-2023**
100	Anar	04-SEP-2023	10-OCT-23	04-SEP-2023
101	Ant	01-OCT-2023	31-DEC-99	01-OCT-2023
**101	Bee	12-APR-2023	01-OCT-2023	12-APR-2023**
101	Bee	04-MAR-2023	10-APR-23	10-APR-23

Note- The thing her is for one id one user should have end_date as 31-DEC-99

Answer 1

I'm assuming that you:

• want to modify the rows only when end_date is 9999-12-31;
• want to display the create_date as the start_date; and
• want to take the next create_date as the end_date if it exists else use the current end_date.

Given that assumption then you can use CASE expressions and the LEAD analytic function:

SELECT id,
       name,
       CASE
       WHEN end_date = DATE '9999-12-31'
       THEN create_date
       ELSE start_date
       END AS start_date,
       CASE
       WHEN end_date = DATE '9999-12-31'
       THEN LEAD(create_date, 1, end_date) OVER (PARTITION BY id ORDER BY create_date)
       ELSE end_date
       END AS end_date,
       create_date
FROM   table_name
ORDER BY id, create_date

Which, for the sample data:

CREATE TABLE table_name (ID, NAME, START_DATE, END_DATE, CREATE_DATE) AS
SELECT 100, 'Apple', DATE '2023-11-01', DATE '9999-12-31', DATE '2023-11-01' FROM DUAL UNION ALL
SELECT 100, 'Anar',  DATE '2023-10-10', DATE '9999-12-31', DATE '2023-09-12' FROM DUAL UNION ALL
SELECT 100, 'Anar',  DATE '2023-09-04', DATE '2023-10-10', DATE '2023-09-04' FROM DUAL UNION ALL
SELECT 101, 'Ant',   DATE '2023-10-01', DATE '9999-12-31', DATE '2023-10-01' FROM DUAL UNION ALL
SELECT 101, 'Bee',   DATE '2023-04-10', DATE '9999-12-31', DATE '2023-04-12' FROM DUAL UNION ALL
SELECT 101, 'Bee',   DATE '2023-03-04', DATE '2023-04-10', DATE '2023-03-04' FROM DUAL;

Outputs:

ID	NAME	START_DATE	END_DATE	CREATE_DATE
100	Anar	2023-09-04 00:00:00	2023-10-10 00:00:00	2023-09-04 00:00:00
100	Anar	2023-09-12 00:00:00	2023-11-01 00:00:00	2023-09-12 00:00:00
100	Apple	2023-11-01 00:00:00	9999-12-31 00:00:00	2023-11-01 00:00:00
101	Bee	2023-03-04 00:00:00	2023-04-10 00:00:00	2023-03-04 00:00:00
101	Bee	2023-04-12 00:00:00	2023-10-01 00:00:00	2023-04-12 00:00:00
101	Ant	2023-10-01 00:00:00	9999-12-31 00:00:00	2023-10-01 00:00:00

fiddle