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:

Lit_Hold_Err table:

CodePudding user response：

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.