CODEWITHSUNDEEP
sqloracle-database
user2051823
user2051823

Reputation: 177

Update records by group

Some records in my table are missing their START_DATE and END_DATE values, now I am trying to use SQL to update them as below:

Before Update

enter image description here

After Update

enter image description here

Grouping by the TYPE column and sorting by END_DATE column, when START_DATE from B1 and the END_DATE from A1 are both NULL, they should be filled by the value of END_DATE from B1.

START_DATE from B1 : FIRST_VALUE(START_DATE) OVER(PARTITION BY TYPE ORDER BY ID ASC)

END_DATE from A1 : LAST_VALUE(END_DATE) OVER(PARTITION BY TYPE ORDER BY ID DESC)

However, I cannot figure out how can I use SQL or PL/SQL to implement the above task. Please help provide a way to do the task.

Upvotes: 1

Views: 88

Answers (1)

user5367577
user5367577

Reputation:

If your sequece is same all the time, then this will work correctly. You have to run 2 update statements. Try running them without commiting the data and see if you get what you want.

update mytable t
set start_date =  (select end_date from mytable aa where aa.id=t.id)
where start_date is null;

update mytable t
set end_date =   (select end_date from mytable aa where aa.id=t.id+1)
where end_date is null;

Upvotes: 3

Related Questions