SQL: Updating a column based on other table

TableA

ID MatchID1 MatchID2
1   1002    120
3   1003    141
5   1006    150
6   1008    140

TableB

ID MatchID1 MatchID2 Status
1   1002     120 
2   1002     120
3   1002     120
4   1003     200
5   1006     150
6   1008     150
7   1008     140

I want to populate TableB col = status with 'FAIL' if: ALL matchID2 for its MatchID1 from tableB is not equal to the matchID2 for its corresponding MAatchID1 in tableA In this case, tableB: 120 corresponds to 1002, same is true for tableA, hence not fail. Expected result:

ID MatchID1 MatchID2 Status
    1   1002     120   NULL
    2   1002     120   NULL
    3   1002     120   NULL
    4   1003     200   FAIL
    5   1006     150   NULL
    6   1008     150   FAIL
    7   1008     140   FAIL

Note: If even one record(match02) is not matching, fail whole set for match01. like for 开发者_如何学Pythonid 6&7 in tableB. Thanks in advance.

UPDATE a
SET Status = 'FAIL'
FROM TableA a
  INNER JOIN (
    SELECT a.MatchID1
    FROM TableA a
      INNER JOIN b ON a.MatchID1 = b.MatchID1 AND a.MatchID2 <> b.MatchID2
    GROUP BY a.MatchID1
  ) x ON a.MatchID1 = x.MatchID1

   update B
    set status = 'FAIL'
    From tableB B
    INNER JOIN (SELECT B.matchID1 FROM TableB B
                GROUP BY B.matchID1
                HAVING MAX(matchID1)<> MIN(MatchID2)) B1
    ON B.matchid1 = B1.MatchID1

UPDATE B
SET Status = 'FAIL'
FROM TableB B
  INNER JOIN TableA A
  ON A.MatchID1 = B.matchID1
WHERE A.matchID2 <> B.matchID2

re-reading.. still don't know what you asked...

suggest breaking down your effort into smaller chunks.

it seems you need several things:

1. a small query to get just the rows from the first table that do or do not have different match numbers. write that.

2. a small query to find if match number from the second table are found in the first table. write that.

3. an update that sets values based on the first two queries.

hth

I think you are looking for an update via a join. Try this;

UPDATE TableB 
SET TableB.Status = CASE WHEN TableA.ID IS NULL THEN 'FAIL' ELSE NULL END
FROM TableB
LEFT JOIN TableA
ON TableB.MatchID1 = TableA.MatchID1
    AND TableB.MatchID2 = TableA.MatchID2 

You didn't say which RDBMS you are using, the above is for SQL Server.