SQL Query; Locate and Disable

Question

I am new at SQL, including performing queries. Everyday, our organization sends a list of employees that were terminated in a listed format.

We get a text file everyday with a list of employee ID's that were terminated, that we need to deactivate in our application's database. I know this is still manual, but I think it is a good start for some automation. We essentially need to do two tasks, find the user in the database in two locations, and deactivate them.

Maybe a function?

1. On the dbo.EnterpriseUser table, we need the code to find the user by looking for the IVRID (Which is the employee's ID sent to us in the listed format) and then update the IsActive field from 1 to 0, and then update the LastModDate to the current date and time using the same format. Below is the example table I am working with, sorry if the formatting is not correct.

    CREATE TABLE IF NOT EXISTS EnterpriseUser (
    `EnterpriseUserID` INT,
    `FirstName` VARCHAR(6) CHARACTER SET utf8,
    `LastName` VARCHAR(7) CHARACTER SET utf8,
    `IVRID` INT,
    `IsActive` INT,
    `LastModDate` DATETIME
 );
 INSERT INTO EnterpriseUser VALUES
    (6,'Robert','Andrews',2,1,'2018-07-11 13:01:54.670000');

2. The second table dbo.Staff must be properly updated as well for the application. My only problem is that to find the record to update, I have to use the EnterpriseUserID field from dbo.EnterpriseUser as it is now in the StaffID field on the dbo.Staff table.

On that row, the LastModDate must be changed to current date and the IsActive must change from 1 to 0.

CREATE TABLE IF NOT EXISTS Staff (
    `StaffID` INT,
    `FirstName` VARCHAR(6) CHARACTER SET utf8,
    `LastName` VARCHAR(7) CHARACTER SET utf8,
    `LastModDate` DATETIME,
    `IsActive` INT
);
INSERT INTO Staff VALUES
    (6,'Robert','Andrews','2018-07-11 13:01:54.670000',1);

Answer 1

To answer question #1:

UPDATE EnterpriseUser
SET IsActive = 0,  LastModDate = NOW()
WHERE 
  IVRID = 2;

To answer question #2:

UPDATE Staff
SET IsActive = 0,  LastModDate = NOW()
WHERE StaffID = 
 (SELECT EnterpriseUserID 
  FROM EnterpriseUser 
  WHERE IVRID = 2);

If I was building these, I would likely have a script that ran through the text file.

You can combine these into a barebones procedure like such:

CREATE PROCEDURE termination (IN inIVRID INT)
BEGIN 
  UPDATE EnterpriseUser
  SET IsActive = 0,  LastModDate = NOW()
  WHERE 
  IVRID = inIVRID;
  UPDATE Staff
  SET IsActive = 0,  LastModDate = NOW()
  WHERE StaffID = 
   (SELECT EnterpriseUserID 
    FROM EnterpriseUser 
    WHERE IVRID = inIVRID);  
  COMMIT;  
END$$