Update value with nested subqueries

Question

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%'
)

• Vérace: Please use the formatting tools at the top of the question edit box - { } for code! This question would be an ideal case for a fiddle as outlined in the post here. p.s. welcome to the forum!

Answer 1

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
;

• Geoff.E: I appreciate the help guys! After making a small change to the line joining table c (c.tablec_id) I was able to get it to run without error but didn't see any records change. I wanted to clarify one thing. Where I put Value1 is really field1 and the value of the field is null for most records.
• Geoff.E: I believe the issue is with the data in TableC I chose to use. I am looking at my select statements to ensure I am looking at all the data I need.