update an employee name
/*------------------------------------------------------------------------------
DATE: 02/27/2012
AUTH: James R. Aylesworth
DESC: This script is designed to update an employee's information when a name
change has occurred
USE: Change 000000 to match employee's ID
Change defaults to new information
Run the script (F5)
** WARNING **
This script commits changes AS IT RUNS! This is so that the trailing tables can
get the new information from the first updated table, EMPLOYEE_PROFILES.
------------------------------------------------------------------------------*/
DEFINE emplID = '123456';
DEFINE new_first_name = 'Jane';
DEFINE new_last_name = 'Doe';
DEFINE new_domain_id = 'jdoe';
DEFINE new_email = 'Jane_Doe@URMC.Rochester.edu';
--update the EMPLOYEE_PROFILES table
UPDATE
(SELECT * FROM EMPLOYEE_PROFILE WHERE EMPLOYEE_ID = &emplID
)
set
FIRST_NAME = '&new_first_name',
LAST_NAME = '&new_last_name',
DOMAIN_ID = '&new_domain_id',
EMAIL = '&new_email';
commit;
--update the EVAL_EMPL_PR table
UPDATE
( SELECT * FROM EVAL_EMPL_PR WHERE EMPL_ID = &emplID
)
SET EMPL_NAME =
(SELECT DISTINCT first_name
|| ' '
|| last_name AS "EMPL_NAME"
FROM employee_profile
where EMPLOYEE_ID = &emplID
);
commit;
--update the EVAL_PG08 table (Strong Commitment)
update
( SELECT * FROM EVAL_PG08 WHERE EMPL_ID = &emplID
)
set DIG_SIGN_NAME =
(SELECT DISTINCT first_name
|| ' '
|| last_name AS "EMPL_NAME"
from EMPLOYEE_PROFILE
where EMPLOYEE_ID = &emplID
);
commit;