CODEWITHSUNDEEP
sqloracle-database
user3809240
user3809240

Reputation: 93

How to compare two tables in best possible way

There is data in two tables with same number of comuns(No primary key)-table a for active records and table b for inactive records of a particular firm. Status 'A' is for active and 'I' for inactive

Both of them have same number of columns.How can I compare the data in table a and table b such that if a record exists in b and not a then the status column in A has to be set to 'I' otherwise 'A'

Eg :-

table A

COL1 COL 2 COL3         STATUS
1    one   this is one   A
2    TO    THIS IS TO    I


Table B

Col1 col2  Col 3        status
3    THREE 33             A
4    for   this is for    A

now in table B col1 with 1 does not exist though it exists in table A with status A. Thus after comparing the Table B and Table A we will

 update table a 
set status ='I'
where col1 =1;

But i am not getting how to compare these two tables ? Kindly help?

Upvotes: 1

Views: 191

Answers (2)

wolφi
wolφi

Reputation: 8361

I'm confused by table a and table b, how can you set the status in table a to 'A' if there is no record in a?

In any case, I'd use MERGE for changing the status column in a table depending on values in another table:

MERGE INTO table_a 
USING table_b
   ON (table_a.col1 = table_b.col1 /* add other columns */)
 WHEN MATCHED THEN 
      UPDATE SET status = 'I' 
       WHERE status <> 'I';

This set the status in table_a if it is found in table_b. You have to specify the columns you use to identify identical columns where it says /* add other columns */. And please watch out for NULL values...

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

To get records that in B and not in A

  select Col1,
         Col2
    from B 

  minus -- <- Oracle/PL SQL specific; other dialects use 'except'

  select Col1,
         Col2
    from A

To update A you can do something like that:

  update A
     set status = case
           when (Col1, Col2) in (
             select B1.Col1,
                    B1.Col2
               from B B1
              minus
             select A1.Col1,
                    A1.Col2
               from A A1) then
             'I'
           else 
             'A'
         end

Upvotes: 0

Related Questions