After rechecking my sandbox, it looks like my code my still be valid, so here it is again.

CREATE TABLE #CandidateTable

(

ID INT IDENTITY PRIMARY KEY,

CandidateID INT,

Campaign INT,

Criteria VARCHAR(10),

[Status] VARCHAR(20)

)

CREATE TABLE #CriteriaTable

(

ID INT IDENTITY PRIMARY KEY,

CriteriaTypeID INT,

Campaign INT,

Criteria VARCHAR(10)

)

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (1, 1, 'ABC')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (1, 2, 'AAA')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (1, 3, 'ASD')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (2, 1, '111')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (2, 2, '333')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (2, 3, 'ASD')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (3, 1, 'ABC')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (3, 2, '333')

INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)

VALUES (3, 3, 'ASD')

INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)

VALUES (1, 1, 'ABC')

INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)

VALUES (2, 1, 'AAA')

INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)

VALUES (1, 2, 'AAA')

INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)

VALUES (2, 2, 'ABC')

INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)

VALUES (2, 3, '333')

INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)

VALUES (3, 3, 'ASD')

UPDATE a

SET a.Status = 'INVALID'

FROM #CandidateTable a

INNER JOIN #CriteriaTable b ON b.Campaign = a.Campaign AND b.Criteria <> a.Criteria AND b.CriteriaTypeID = 1

SELECT * FROM #CandidateTable

SELECT * FROM #CriteriaTable WHERE CriteriaTypeID = 1

DROP TABLE #CandidateTable

DROP TABLE #CriteriaTable