1

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 '18 at 22:45
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
;
  • 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 '18 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 '18 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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