CODEWITHSUNDEEP
oracle-databaseplsql
Joel
Joel

Reputation: 438

ORACLE Update with MINUS result

In Oracle 10g, I want to update the records of the resulting minus query below: 

(
 SELECT A,B,C FROM Table1

  MINUS

 SELECT A,B,C FROM Table2
)

The column that is to be updated is not part of the minus query as its not present in both tables so the below code is not an option

UPDATE
(
 SELECT A,B,C FROM Table1

  MINUS

 SELECT A,B,C FROM Table2
)
SET TABLE1.D = 'TEST'

Upvotes: 0

Views: 2618

Answers (2)

GriffeyDog
GriffeyDog

Reputation: 8376

In response to your comment about wanting to use the minus clause:

update Table1
  set d = 'TEST'
  where (a,b,c) in (select a,b,c from Table1 minus select a,b,c from Table2);

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

How about:

update table1
   set d = 'TEST'
 where (a,b,c) not in(select a,b,c from table2);

Edit: The performance of minus generally suck, due to the sort operation. If any of {a,b,c} are nullable, try the following instead:

update table1 t1
   set t1.d = 'TEST'
 where not exists(
         select 'x'
           from table2 t2
          where t2.a = t1.a
            and t2.b = t1.b
            and t2.c = t1.c
       );

Upvotes: 1

Related Questions