UPDATE `ohrm_notification_subscriber` nsb
LEFT JOIN `ohrm_notification_subscriber_subscription` nss ON nsb.id=nss.subscriber_id
LEFT JOIN `ohrm_notification_subscription` nsc ON nsc.id=nss.subscription_id
LEFT JOIN `ohrm_notification` n ON nsc.notification_id=n.id
SET nsb.name=CASE
	WHEN nsb.name = 'AppraisalReviewer.self' THEN 'Appraisal Reviewer - Self Evaluator'
    WHEN nsb.name = 'AppraisalReviewer.mainEvaluators' THEN 'Appraisal Reviewer - Main Evaluator'
    WHEN nsb.name = 'AppraisalReviewer.supervisors' THEN 'Appraisal Reviewer - Supervisor'
    WHEN nsb.name = 'AppraisalReviewer.peers' THEN 'Appraisal Reviewer - Peer'
    WHEN nsb.name = 'AppraisalReviewer.subordinates' THEN 'Appraisal Reviewer - Subordinate'
    WHEN nsb.name = 'ess' THEN 'Affected Employee'
    ELSE nsb.name
    END
WHERE n.name IN ('Employee Appraisal Initiated Notification','Employee Appraisal Completed Notification', 'Employee Appraisal Submitted Notification', 'Employee Appraisal Rejected Notification', 'Employee Appraisal Signed Notification') AND nsb.type='Custom';


DELETE et FROM ohrm_email_template et
LEFT JOIN ohrm_email e ON et.email_id=e.id WHERE e.name IN ('appraisal.completed','appraisal.initiated', 'appraisal.rejected', 'appraisal.signed', 'appraisal.submitted', 'Appraisal Due Date Notifications');

DELETE en FROM  ohrm_email_notification en
LEFT JOIN ohrm_email e ON en.email_id=e.id WHERE e.name IN ('appraisal.completed','appraisal.initiated', 'appraisal.rejected', 'appraisal.signed', 'appraisal.submitted', 'Appraisal Due Date Notifications');

DELETE ep FROM  ohrm_email_processor ep
LEFT JOIN ohrm_email e ON ep.email_id=e.id WHERE e.name IN ('appraisal.completed','appraisal.initiated', 'appraisal.rejected', 'appraisal.signed', 'appraisal.submitted', 'Appraisal Due Date Notifications');

DELETE FROM ohrm_email WHERE `name` IN ('appraisal.completed','appraisal.initiated', 'appraisal.rejected', 'appraisal.signed', 'appraisal.submitted', 'Appraisal Due Date Notifications');