0

I have a table called batch_history with below data. The table consists of data for different batches (batch ids will vary). I would like to update the current_status_start_date to the rec_change_date of the previous row. I want this to get updated only for records with current_status_id as 3310 and 3320. This should be done for all the batches (In screenshot I have just shown the data of one batch) available in batch_history table with data_stream_id as 129.

Current Data in batch_history table:

enter image description here

Needed output in batch_history table:

enter image description here

I have gone through queries using 'WITH' clause but still I am unable to figure out the exact usage and solution for this issue. Please help out. Thanks in advance.

prabu R
  • 2,099
  • 12
  • 32
  • 41
  • See also [Get Value from Previous row/next row SQL](https://stackoverflow.com/questions/25667392/get-value-from-previous-row-next-row-sql) and [In PL/SQL, how do you update a row based on the next row?](https://stackoverflow.com/questions/4156058/in-pl-sql-how-do-you-update-a-row-based-on-the-next-row) – Vadzim May 08 '20 at 11:37

1 Answers1

2

You can do it like this:

UPDATE batch_history t  
SET t.current_status_start_date = NVL((SELECT max(s.rec_change_date) FROM batch_history s
                                       WHERE s.rec_change_date < t.rec_change_date 
                                         and t.batch_id = s.batch_id),
                                      current_status_start_date )
sagi
  • 40,026
  • 6
  • 59
  • 84
  • Thanks Sagi. It's updating the current_status_id with the rec_change_date of next row. But, I need it to get updated with the previous row's value. – prabu R Mar 29 '16 at 09:52