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%'
  • 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!
    – Vérace
    May 16, 2018 at 22:45

1 Answer 1


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


 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
    b.class in ( '1', '2' )
  AND c.name NOT LIKE 'MPG%'
) b1
ON (a1.TableA_ID=b1.TableA_ID)
SET a1.value=b1.value
  • 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
    May 17, 2018 at 13:53
  • 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.
    – Geoff.E
    May 17, 2018 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.