UPDATE mcs.CHANNEL_AUD ch1 
SET SERVICE_STATUS=(SELECT SERVICE_STATUS
                      from mcs.CHANNEL_AUD CH 
                     WHERE ch.SERVICE_STATUS IS NOT NULL 
                       and ch1.SRC_CMS_ID=ch.SRC_CMS_ID
                       and not exists (select 1 from mcs.CHANNEL_AUD CH_ex 
                                        where CH.name = CH_ex.name 
                                          and CH_ex.SERVICE_STATUS IS NOT NULL
                                          and (CH_ex.rev < CH.rev 
                                               or CH_ex.rev = CH.rev and CH_ex.rowid < CH.rowid)
                                       )
                     )
WHERE SERVICE_STATUS IS NULL