# Update a Person record ;
# This script describes how to update persons  from the CTFS/ForestGeo database
# Shameema Esufali June 2020
-- **********************************************************************************************
This part sets up the test database.  You must replace it with a USE current_yoursite;
*/
DROP DATABASE IF EXISTS metrop;
CREATE DATABASE IF NOT EXISTS metrop;
USE metrop;
SOURCE ../sampledatabases/metrop.sql;
-- end test database setup **********************************************************************

 
SET @oldfirstname="Georgeann", @oldlastname="Jarboe";

-- Change a name

# 2. Update the persons record ************************************************

UPDATE Personnel 
 SET firstname="Georgiana"
 WHERE FirstName =@oldfirstname AND LastName =@oldlastname;

# Note the where clause for the operation matches the where clause for 
# selecting the records to log.
# *****************************************************************************


-- Change a role 
SET @firstname="Maritza", @lastname="Freeman", @oldrole="field technician";
SET @newrole="field supervisor";

SELECT @oldroleid:= roleid FROM RoleReference WHERE Description=@oldrole;
SELECT @personnelid:=personnelid FROM Personnel 
 WHERE firstname=@firstname AND lastname=@lastname;

SELECT @newroleid:=roleid FROM RoleReference WHERE Description=@newrole;
       
  
# 3. Update the record linking the person with the role. **********************
UPDATE PersonnelRole SET roleid=@newroleid  
 WHERE personnelid=@personnelid and roleid =@oldroleid;     
 
 # Note you may log these in the usual way if you choose.   
 # Please refer other sample scripts for method. 
