Update value with nested subqueries

Posted on

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

Answer :

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
;

Leave a Reply

Your email address will not be published. Required fields are marked *