I’m trying to write a script and do the following steps in my db.

1. Read records (where CreatedDate = 06/06/2022 AND Status = Processed) from Daily_Proc table.

2. Check if any particular record is also existed in Lit_Hold_Err table using ‘MID’ and ‘Source’ columns value.

3. 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 look like this

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 look like this

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

>Solution :

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.