SQL Server: comparing two tables, two columns

I have two tables.

• TableA (ID int, Match1 char, Match2 char, status char)
• TableB (Match1 char, match2)

I want to flag rows in tableA as fail in status where for same match1, match2 do not exist in tableB.

Example:

TableA

ID Match1 Match2 Status
1   100    AB    
2   100    AR
3   200    BC
4   200    VB
5   200    AB

TableB

Match1 Match2
100     AF
100     AR
100     BG
200     AB
200     BJ
200     VB

Expected result:

TableA

ID Match1 Match2 Status
1   100    AB    FAIL 
2   100    AR    NULL
3   200    BC    FAIL
4   200    VB    NULL
5   200    AB    NULL

Thanks

I used(not working):

Update A
set status = 'FAIL'
from TableA A
  Inner join TableB开发者_JAVA技巧 B
  ON A.match1 = B.match1
  WHERE A.match2 <> B.Match2

---

UPDATE a
    SET status = 'FAIL'
    FROM TableA a
    WHERE NOT EXISTS(SELECT NULL
                         FROM TableB b
                         WHERE a.match1 = b.match1
                             AND a.match2 = b.match2)

---

Try this:

DECLARE @TableA TABLE (ID INT, Match1 VARCHAR(10), Match2 VARCHAR(10), MatchStatus VARCHAR(10))

INSERT INTO @TableA(ID, Match1, Match2) 
VALUES(1, '100', 'AB'), (2, '100', 'AR'), (3, '200', 'BC'), (4, '200', 'VB'), (5, '200', 'AB')

DECLARE @TableB TABLE (Match1 VARCHAR(10), Match2 VARCHAR(10))

INSERT INTO @TableB VALUES('100', 'AF'), ('100', 'AR'), ('100', 'BG'), ('200', 'AB'),
('200', 'BJ'), ('200', 'VB')

UPDATE @TableA
SET MatchStatus = 'FAIL'
WHERE NOT EXISTS
          (SELECT * FROM @TableB b 
           WHERE b.Match1 = [@TableA].Match1 AND b.Match2 = [@TableA].Match2)

SELECT * FROM @TableA

Gives me an output of:

ID  Match1  Match2  MatchStatus
 1   100     AB       FAIL
 2   100     AR       NULL
 3   200     BC       FAIL
 4   200     VB       NULL
 5   200     AB       NULL