Update fields using fields from another row

Vote count:

0

I have a 2 rows:

• Row #1 (LN_DISB_SEQ_NBR = 3) This row has the field data I need


• Row #2 (max(LN_DISB_SEQ_NBR) = 6) This row has fields I want to update

Example of what I have (not the full table):

LN_DISB_SEQ_NBR | EMPLID  | DNT_CHNG_THS | LN_ACTION_DT | TRNSFR_BATCH | more...
----------------+---------+--------------+--------------+--------------+--------
              3 | 1881530 |          abc |  13-JAN-2015 | 920399999203 |
              6 | 1881530 |          xyz |  14-JAN-2015 | 950000000000 | 

Example of what I want the update to be:

LN_DISB_SEQ_NBR | EMPLID  | DNT_CHNG_THS | LN_ACTION_DT | TRNSFR_BATCH | more...
----------------+---------+--------------+--------------+--------------+--------
              3 | 1881530 |          abc |  13-JAN-2015 | 920399999203 |
              6 | 1881530 |          xyz |  13-JAN-2015 | 920399999203 | 

Here's the issue I'm seeing: Right now, my SQL works golden, but I'm not happy with the way it's constructed. If I want to add more fields to change, it will continue to grow in size and continually get more difficult to maintain (having to change a number of fields).

How can I re-write/condense this so it performs the same task, but is more efficient and cleaner?

update PS_AG_LOAN_DISB_ACTN3 t1
   set t1.LN_ACTION_STATUS = 'B',
       t1.LN_ACTION_DT = (select LN_ACTION_DT
                            from PS_AG_LOAN_DISB_ACTN3
                           where EMPLID = '1881530'
                             and AID_YEAR = '2015'
                             and ACAD_CAREER = 'UGRD'
                             and ITEM_TYPE = '913000300110'
                             and DISBURSEMENT_ID = '02'
                             and LN_ACTNSTAT_DT >= '13-Jan-15'
                             and LN_DISB_SEQ_NBR = 3),
       t1.TRNSFR_BATCH = (select TRNSFR_BATCH
                            from PS_AG_LOAN_DISB_ACTN3
                           where EMPLID = '1881530'
                             and AID_YEAR = '2015'
                             and ACAD_CAREER = 'UGRD'
                             and ITEM_TYPE = '913000300110'
                             and DISBURSEMENT_ID = '02'
                             and LN_ACTNSTAT_DT >= '13-Jan-15'
                             and LN_DISB_SEQ_NBR = 3                    
 where t1.EMPLID = '1881530'
   and t1.LN_DISB_SEQ_NBR = (select max(LN_DISB_SEQ_NBR)
                               from PS_AG_LOAN_DISB_ACTN3
                              where EMPLID = '1881530'
                                and AID_YEAR = '2015'
                                and ACAD_CAREER = 'UGRD'
                                and ITEM_TYPE = '913000300110'
                                and DISBURSEMENT_ID = '02'
                                and LN_ACTNSTAT_DT >= '13-Jan-15'
                                and LN_ACTION_STATUS = 'R');

asked 42 secs ago

O P

419

Update fields using fields from another row