I am working in Oracle and trying to update a value in tableA to = ‘Y’ based on info from two other tables (B and C)
I have tried something like this which does work if I have only the first select statement but I need to exclude some records by using the second select statement.
update tableA set value1 = 'Y' where tableB_ID in ( select ID from tableB where class in ('1','2') ) and tableC_ID in ( select ID from tableC where name not like 'MPG%' )
UPDATE statements, with complex
WHERE clauses, are easier to understand, and maintain, when written as a
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 ;