Andrew Andrew - 6 months ago 10
SQL Question

Selecting rows based on multiple references in second table

Here's a representative sample of data. In the first table rows 2 and 3 are incorrect. A row in Table 1 should be active only when there exists a row with status B and there does not exist a row with status C in Table 2.

+----+--------+--+ +-----+-----+--------+
| ID | ACTIVE | | | ID | REF | STATUS |
+----+--------+--+ +-----+-----+--------+
| 1 | 0 | | | 9 | 1 | A |
| 2 | 1 | | | 100 | 2 | A |
| 3 | 1 | | | 103 | 2 | B |
| 4 | 1 | | | 104 | 2 | C |
+----+--------+--+ | 111 | 3 | A |
| 123 | 4 | A |
| 126 | 4 | B |
+-----+-----+--------+


How can I set active in rows 2 and 3 to 0 based on those rules?

Answer

If you only want to update the rows that should be zero and leave everything else alone than you can use

              UPDATE TABLE_A A
                 SET A.ACTIVE = 0
               WHERE A.A_ID IN
                                ( SELECT b.ref
                                   FROM TABLE_B B
                                  WHERE NOT EXISTS (SELECT 1
                                           FROM TABLE_B
                                          WHERE STATUS = 'B'
                                            AND B.REF = REF)
                                     OR EXISTS
                                  (SELECT 1
                                           FROM TABLE_B
                                          WHERE STATUS = 'C'
                                            AND B.REF = REF))
                                 AND a.active<>0;