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);

Great formatting for your second question. Thanks for the DDL — Zerodf, Jul 12 '18 at 12:00

score 0 · Accepted Answer · answered Jul 12 '18 at 11:54

0

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$$

answered Jul 12 '18 at 11:54

Zerodf

2,208
18
26

The updates worked perfect, thank you! However, I am having an issues trying to run the procedure. Can you please let me know how to run the procedure through a text file? Like in the procedure, where does the data for `IVRID` go? Just trying to learn as much as I can! – Josh Jul 12 '18 at 12:40
For a procedure, you would just use `CALL termination(2)`. The procedure might need a little bit of tweaking as MySQL is not my primary engine of expertise. To run over a text file, I would likely use some scripting language (Python most likely) to iterate over all of the lines in the daily text file. I would then insert the IVRID values into the SQL code above and execute them against the database. – Zerodf Jul 12 '18 at 12:49

SQL Query; Locate and Disable

1 Answers1