Populating values to null column based on value of some another column from another row

Question

My requirement is pick HIST_START value where status="CANCELLED" AND table="OUTCOME_STATUS". Put this value in HIST_END column where table=("DOC_STATUS" OR "ENTRY_STATUS") AND HIST_END is NULL Eg. The first and second rows should have HIST_END as 12/06/2012 (which is HIST_START of last row.

ID          HIST_START   HIST_END    Appl Number    code    version STATUS   TABLE
10298337    4/06/2012                 1             I45     1   PENDING      DOC_STATUS
10298337    4/06/2012                 1             I45     1   PENDING      ENTRY_STATUS
10298337    4/06/2012    5/06/2012    1             I45     1   PENDING      OUTCOME_STATUS
10298337    5/06/2012    11/06/2012   1             I45     1   ADM_REV      OUTCOME_STATUS
10298337    11/06/2012   11/06/2012   1             I45     1   BAMERROR     OUTCOME_STATUS
10298337    11/06/2012   12/06/2012   1             I45     1   AWAIT_DOCS   OUTCOME_STATUS
10298337    12/06/2012                1             I45     1   CANCELLED    OUTCOME_STATUS

Thanks in advance..

Answer 1

I believe that the status="CANCELLED" AND table="OUTCOME_STATUS" will give only one row for each ID in this table. But for safer side I am using Max() while fetching hist_start column value.

Then you can use following update statement to do your job -

UPDATE Table_Name t_1
   SET Hist_End = (SELECT MAX(t_2.Hist_Start)
                     FROM Table_Name t_2
                    WHERE t_2.Status = 'CANCELLED'
                      AND t_2."TABLE" = 'OUTCOME_STATUS'
                      AND t_2.Id = t_1.Id)
 WHERE t_1."TABLE" IN ('DOC_STATUS', 'ENTRY_STATUS')
   AND t_1.Hist_End IS NULL;