Update value with nested subqueries
I find UPDATE statements, with complex WHERE clauses, are easier to understand, and maintain, when written as a MERGE statement.

Examples

MERGE INTO TableA a1
USING (
 SELECT TableA_ID, 'Y' value1
 FROM TableA a
 JOIN TableB b ON a.TableB_ID=b.TableB_ID
  JOIN TableC c ON a.TableC_ID=TableC_ID
WHERE
    b.class in ( '1', '2' )
  AND c.name NOT LIKE 'MPG%'
) b1
ON (a1.TableA_ID=b1.TableA_ID)
WHEN MATCHED THEN UPDATE
SET a1.value=b1.value
;