SQL Query; Search List from List

Question

I am new at SQL, including performing queries. Everyday, our organization sends a list of employees that were terminated in a listed format. What I am looking to do is two things, search for the employee ID(s) in a column and if the employee is found, update a certain cell/field to disable.

For instance, if we get a list that has an employee ID's of 123456 and 234567, I want to find those employee ID's in the EMPID field and then update their ACTIVE field from '1' to '0'.

What is the best possible way to do this?

EDIT, sorry for all the missing information as I am completely new to the forum and the coding world, and I'm, going to re-explain what I am trying to accomplish. Maybe a function? I changed the data below to be more consistent with what I am working with versus examples.

We get a text file everyday with a list of employee ID's that were terminated, that we need to deactivate in our application. I know this is still manual, but I think it is a good start for some automation.

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

You can do that using an update statement like:

update Employees
set Active = 0
where EmpId in (123456, 234567)

Answer 2

You can use an update statement if you're going to check them manually.

If you're inserting the employees in the table you can create a trigger to check these employees every time you make an insert.

Answer 3

The OP asked for the the best possible way to do this, and I've never met a process where the best possible solution involved hand keying data.

Import your list into a holding table. Use that table to find and update the affected records. Archive the list, with a datestamp on each record indicating when they were processed.

This uses SQL Server syntax, but there's nothing fancy going on. Easy to translate to a different dialect.

CREATE TABLE dbo.DailyTerminations (
  EnterpriseUserID INT
  );

BEGIN TRANSACTION --By using a transaction, both tables will update or neither will.  
                  --For MySQL, it's START TRANSACTION

  UPDATE eu
    SET IsActive = 0
  FROM 
    dbo.EnterpriseUser as eu
  JOIN
    dbo.DailyTerminations as dt
      ON dt.EnterpriseUserID = eu.EnterpriseUserID
  WHERE 
    eu.IsActive <> 0;

  UPDATE st
    SET IsActive = 0
  FROM 
    dbo.Staff as st
  JOIN 
    dbo.EnterpriseUser as eu
      ON
        eu.EnterpriseUserID = st.StaffID
  JOIN
    dbo.DailyTerminations as dt
      ON dt.EnterpriseUserID = eu.EnterpriseUserID
  WHERE 
    IsActive <> 0;

COMMIT TRANSACTION --For MySQL, it's just COMMIT

INSERT INTO dbo.TerminationArchive
(
 EnterpriseUserID,
 ProcessedDate
)
SELECT
  EnterpriseUserID,
  GETDATE()
FROM
  dbo.DailyTerminations;

TRUNCATE TABLE 
  dbo.DailyTerminations;