SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Daily_Proc (
    Hold            VARCHAR(30),
    MID             VARCHAR(30),
    Source          VARCHAR(30), 
    CreatedDate     DATE,
    Status          VARCHAR(30)
);
INSERT INTO Daily_Proc VALUES
('JE', 'JELEEK', 'JELEEK@gmail.com', '06/03/2022', 'New'      ),
('KE', 'KEKELO', 'KEKELO@gmail.com', '06/06/2022', 'Processed'),
('ZE', 'ZEKEKE', 'ZEKEKE@gmail.com', '06/06/2022', 'Processed');

SELECT * FROM Daily_Proc;


Hold
MID
Source
CreatedDate
Status


JE
JELEEK
JELEEK@gmail.com
2022-06-03
New

KE
KEKELO
KEKELO@gmail.com
2022-06-06
Processed

ZE
ZEKEKE
ZEKEKE@gmail.com
2022-06-06
Processed


CREATE TABLE Lit_Hold_Err(
    Hold            VARCHAR(30),
    MID             VARCHAR(30),
    Source          VARCHAR(30),
    ErrorMessage    VARCHAR(30)
);
INSERT INTO Lit_Hold_Err VALUES
('KE', 'KEKELO', 'KEKELO@gmail.com', 'Not Found');   

SELECT * FROM Lit_Hold_Err;


Hold
MID
Source
ErrorMessage


KE
KEKELO
KEKELO@gmail.com
Not Found


UPDATE Daily_Proc 
SET Status = 'Error-Retry'
FROM       Daily_Proc 
INNER JOIN Lit_Hold_Err
        ON Daily_Proc.MID = Lit_Hold_Err.MID
       AND Daily_Proc.Source = Lit_Hold_Err.Source
WHERE Daily_Proc.CreatedDate = '06/06/2022' 
  AND Daily_Proc.Status = 'Processed';
1 rows affected

SELECT * FROM Daily_Proc


Hold
MID
Source
CreatedDate
Status


JE
JELEEK
JELEEK@gmail.com
2022-06-03
New

KE
KEKELO
KEKELO@gmail.com
2022-06-06
Error-Retry

ZE
ZEKEKE
ZEKEKE@gmail.com
2022-06-06
Processed

Hold	MID	Source	CreatedDate	Status
JE	JELEEK	JELEEK@gmail.com	2022-06-03	New
KE	KEKELO	KEKELO@gmail.com	2022-06-06	Processed
ZE	ZEKEKE	ZEKEKE@gmail.com	2022-06-06	Processed