How to check with different table and update it in SQL Server

Question

I'm trying to write a query that accomplishes to these rules:

• read records (where CreatedDate = 06/06/2022 AND Status = Processed) from Daily_Proc table.
• check if any particular record is also existed in Lit_Hold_Err table using "MID" and "Source" columns value.
• if a particular record is existed in Lit_Hold_Err table then update Status from "Processed" to "Error-Retry" in Daily_Proc table.

I'm able to do the first step and second step but not sure how to do step 3.

SELECT * 
FROM Daily_Proc
WHERE CreatedDate > '06/06/2022 0:00:00'
  AND Status = 'Processed'
  AND (MID in (SELECT MID
               FROM Lit_Hold_Err)
  AND Source In(Select Source
                From Lit_Hold_Err))

Daily_Proc table:

Hold	MID	Source	CreateDate	Status
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

Lit_Hold_Err table:

Hold	MID	Source	ErrorMessage
KE	KEKELO	KEKELO@gmail.com	"Not Found

Answer 1

You may want to build your UPDATE statement using a JOIN operation, that matches the two tables Daily_Proc and Lit_Hold_Err on the MID and Source columns shared by both. Other conditions on DailyProc.CreatedDate and DailyProc.Status can be positioned inside the WHERE statement.

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

Check the demo here.