ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-yyyy hh24:mi:ss';

/*

SELECT a.CUSTOMER_ID, a.DDN, a.END_DAT, a.STATUS
  FROM servista.loved_ones a
 WHERE update_type <> 'DELETED'
   AND end_dat IS NOT NULL
   AND status = 'PENDING'
   AND EXISTS (
          SELECT 1
            FROM servista.loved_ones b
           WHERE b.customer_id = a.customer_id
             AND b.ddn = a.ddn
             AND b.status IN ( 'PROCESSED', 'ERROR' ));

*/

UPDATE servista.loved_ones a
   SET status = 'ERROR'
 WHERE update_type <> 'DELETED'
   AND end_dat IS NOT NULL
   AND status = 'PENDING'
   AND EXISTS (
          SELECT 1
            FROM servista.loved_ones b
           WHERE b.customer_id = a.customer_id
             AND b.ddn = a.ddn
             AND b.status IN ( 'PROCESSED', 'ERROR' )
			);

commit;

/*

SELECT a.CUSTOMER_ID, a.DDN, a.END_DAT, a.STATUS
  FROM servista.loved_ones a
 WHERE update_type <> 'DELETED'
   AND end_dat IS NOT NULL
   AND status = 'PENDING'
   AND EXISTS (
          SELECT 1
            FROM servista.loved_ones b
           WHERE b.customer_id = a.customer_id
             AND b.ddn = a.ddn
             AND b.status IN ( 'PROCESSED', 'ERROR' ));

*/
		
	
